Hello all you clever people (hopefully working late like me!) - can anyone tell me if there is a short cut in Excel to copy the data from the cell above ? Like the F6 key in Sage? Thanks in advance.
At last, someone asking questions about software other than sage
Not sure that I'm answering the question that's being asked but you could either put =cell ref (i.e in B1 put =A1) or grab the bottom right hand corner of the first cell and drag it down as many copies as you want.
Beware though that if the first cell includes calculations the calculations will be incremented when you drag it.
If you want to always refer back to a cell without incrementing use $'s. i.e =a1*$a$2. Then if you drag is the first part will increment (a1, b1, c1, etc. but the second part will remain constant.
Hope that I answered the question that you were asking!
kind regards,
Shaun.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Take the mouse pointer to the bottom right hand corner of the cell(s) you wish to copy down, it should turn into a bold +, hold the left mouse button down and drag or if on laptop the left button on the touchpad. If it's numbers it may add on to the result but it is counter-intuitive so if you have several numbers if you highlight a couple it will copy exactly then it will 'remember' and do that for thereon in. If it's text it will copy the text as is. If it's days or the week or months of the year it will increment them by one, unless you highlight a couple first.
I'm pretty quick off the mark at the mention of Excel... Was begining to feel as though the site had turned into the Sage help desk!
I feel more confident that I was answering the question being asked now that you gave the same response.
All the best,
Shaun.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Thanks Both of You, I was looking for a one-button option ideally. I use the drag method a lot but as you rightly point out it increases the values. I've got about 30 pages of bank statement to bung in to Excel and wanted to just copy the date from above.
Sorry, my original question wasn't very clear was it! And I think I've posted in the wrong section too! That'll teach me to do too many things at once!
Excel help talks about setting upa macro, but I could key it in quicker than that. Perhaps I'll have a go tomorrow when I've not been sat number crunching for 10 hours!
Copying dates is no problem you can use the drag option just enter the date a couple of times and highlight both cells before dragging down. Don't worry about which section as regulars like Shaun and me always look at recent posts rather than a particular section.
if you want a load of entries with the same date put the same date twice then grab both cells and drag.
to increment by a day as you say, ensure that it's in date format then use =a1+1 and it will add 1 day.
Sometimes Excel can try to be too useful and what's right for one scenario is totally wrong for another.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
If it's just a quick way to copy things then "Ctrl D" will copy the cell(s) above. It will also copy the formatting which you may or may not want. I sometimes use it as a quick way to copy the formatting and I'll then change the cell contents.
"Ctrl @" will copy the formula from the cell above but not the formatting. It will only copy one cell, not all the highlighted cells like "Ctrl D". This won't change the cell references if part of a formula either unlike "Ctrl D".
__________________
Never buy black socks from a normal shop. They shaft you every time.
The problem with Ctr-d in Excel as an equivalent to Sage's F6 is that there's something it doesn't do: When you hit F6 to copy from above in Sage, it also moves you to the next field.
This Excel macro redefines Ctrl-d to do that:
----8<----
Sub CopyAboveMoveRight() ' ' CopyAboveMoveRight Macro ' Copy above cell contents, move cursor right one cell ' ' Keyboard Shortcut: Ctrl+d ' ActiveCell.Offset(-1, 0).Range("A1").Select Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.Offset(0, 1).Range("A1").Select End Sub ----8<----
Like the original Ctrl-d it copies the cell contents - so formulae are copied (relatively) as usual.
Also, another F6 feature in Sage is that if you press Shift and F6, it copies the field above, but *increments* it by one. With that in mind, how about a Ctrl-d equivalent for Excel? i.e. a Shift-Ctrl-d:
----8<----
Sub IncAboveMoveRIght() ' ' IncAboveMoveRIght Macro ' Copy and increment above (Value not formula), move right one cell ' ' Keyboard Shortcut: Ctrl+Shift+D ' ActiveCell.FormulaR1C1 = "=R[-1]C+1" ActiveCell.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveCell.Offset(0, 1).Range("A1").Select End Sub ----8<----
There were two options, here:
1) Make the cell contain a formula which adds 1 to the cell above 2) Make the cell contain the actual value + 1.
I opted for 2: It initially puts in the formula to add 1. It then copies it and performs a 'paste special' to paste only the resulting value back in.
(To make it just insert a formula that adds 1, and then move right, simply delete everything from ActiveCell.Select to Application.CutCopyMode = False)
Quiet day. :)
-- Edited by VinceH on Tuesday 27th of March 2012 01:00:03 PM
__________________
Vince M Hudd - Soft Rock Software
(I only came here looking for fellow apiarists...)
Vince - I am in awe of your mastery of the dark art that is Excel. I thought I was pretty cool being able to use V Look Up but I can see I am truly only in the Second Division compared to you in the Premiership!
I do love this forum, and I look forward to the day when I can post some answers instead of just questions!
Vince - I am in awe of your mastery of the dark art that is Excel. I thought I was pretty cool being able to use V Look Up but I can see I am truly only in the Second Division compared to you in the Premiership!
I do love this forum, and I look forward to the day when I can post some answers instead of just questions!
In that case I'm in the North West Counties Football League - Division One.
__________________
Never buy black socks from a normal shop. They shaft you every time.
The truth is I rarely, if ever, use Excel, for a number of reasons. I have MS Office 2007, but I tend to use OpenOffice in preference these days. I was a much 'bigger' Excel user back in the mid-late 90s, when I used the Office 95 version a lot.
Writing a macro is fairly easy when it's stuff like the two I've done above: You just tell Excel to record your actions - carry out the actions you want it to record - then tell it to stop recording.
To see the program code - for example to paste it into the forum, like I've done - you 'edit' the macro.
Understanding the program code is another matter: But I'm a geek who's been programming computers since school, which was some time during the Roman occupation. ;)
__________________
Vince M Hudd - Soft Rock Software
(I only came here looking for fellow apiarists...)
