Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
jake
 
Posts: n/a
Default pasting or moving formula cells without updating formulas

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
  #2   Report Post  
Fredrik Wahlgren
 
Posts: n/a
Default


"jake" wrote in message
...
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




  #3   Report Post  
jake
 
Posts: n/a
Default

perfect, thankyou


"Fredrik Wahlgren" wrote:


"jake" wrote in message
...
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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
What formula is used for subtracting a range of different cells f. tim Excel Worksheet Functions 3 April 21st 23 11:07 PM
simple formula with blank cells Brian Excel Worksheet Functions 1 April 1st 05 05:41 AM
Applying formula to only NON-EMPTY cells in range Tasi Excel Discussion (Misc queries) 5 March 29th 05 11:48 PM
How do I set up a formula to repat 5 consecutive cells in a colum. jbsand1001 Excel Worksheet Functions 2 January 7th 05 09:36 PM
Formula not updating Paul Excel Worksheet Functions 2 November 6th 04 02:05 AM


All times are GMT +1. The time now is 07:26 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"