pasting or moving formula cells without updating formulas

[Guest]

I have a flat spreadsheet with a results page at the end. The results page
contains a set of formulae which refer to various cell locations within the
body of the spreadsheet in order to return statistical results based on the
values in said cells. Now I'd like to add more data to my spreadsheet, so i
need to make it bigger; however, when I copy and paste, or select and drag
the cells containing the formulae, Excel updates the formulae so that they
refer to different cells which bear the same spatial relationship to the
formulae as the original referees did before the formulae were moved.
Normally this is a very useful function, but in this case it's a royal pain
in the arse. If anyone could tell me how to move my formulae and still have
them refer to the same cells I'd be most grateful and probably keep a little
of my hair a little longer.

Thanks,

Jake

 

[Fredrik Wahlgren]

I have a flat spreadsheet with a results page at the end. The results page
contains a set of formulae which refer to various cell locations within the
body of the spreadsheet in order to return statistical results based on the
values in said cells. Now I'd like to add more data to my spreadsheet, so i
need to make it bigger; however, when I copy and paste, or select and drag
the cells containing the formulae, Excel updates the formulae so that they
refer to different cells which bear the same spatial relationship to the
formulae as the original referees did before the formulae were moved.
Normally this is a very useful function, but in this case it's a royal pain
in the arse. If anyone could tell me how to move my formulae and still have
them refer to the same cells I'd be most grateful and probably keep a little
of my hair a little longer.

Thanks,

Jake

Snipped from http://www.mrexcel.com/tip048.shtml

a.. $A1 tells Excel you always want to refer to column A.
a.. (If you dragfill downwards, you get $A2)
a.. B$1 tells Excel you always want to refer to row 1.
a.. (If you dragfill to the right, you get C$1)
a.. $B$1 tells Excel you always want to refer to cell B1.

/Fredrik