Cut and paste formulas - keep same as orginal, possible?

[mattkorguk]

Hi,
I'm sure there is a simple explanation but when I copy and paste a column which contains formulas, it wants to update the formulas to the new location.
Is there a way to stop this?
I'd like to be able to truely copy and paste, so the same formula details are pasted. I thought there might be something under paste special but I have tried all the options I'm sure.
Background - Monthly figures are entered onto the spreadsheet where each month has conditional formatting/ formulas against the January entry. Each month is then completed and when a month has ended a new column is created (here's the issue) by copy and pasting the last month into a new column. Then lasts months figures/ column is hidden so you'll just have January and say July next to each other on the worksheet.
Thanks

 

[hardyd44]

use the dollar sign

Hi,

If you put a dollar sign "$" before the cell reference eg: instead of A3 you use $A$3 the formula value stays the same when you copy and paste

better example: Sum(A3+A4) in cell B3 when copied and pasted to C3 will become Sum(B3+B4) however, Sum($A$3+$A$4) in cell B3 when copied and pasted to C3 will stay as Sum($A$3+$A$4)

Hope this helps

cheers

Dean

 

[mattkorguk]

That works a treat, thank you very much Dean.
Matt

 

[hardyd44]

Your Welcome

No Problem - it was things like that in excel, that made me start using access more and more

BTW: if you want to paste just calculated values not formulas try paste special, values.

Dean

 

[namliam]

if you dont want to add the $ your self you can hit the F4 button... this changes between the different options of
A4
$A$4
$A4
A$4
and back to A4

Have fun!

 

[hardyd44]

cool

namliam,

did not know that one - learn something new every day

 

[namliam]

Thats what these forums are for

 

[mattkorguk]

That saves a little time for sure, thanks all.