Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]() "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
![]() |
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
What formula is used for subtracting a range of different cells f. | Excel Worksheet Functions | |||
simple formula with blank cells | Excel Worksheet Functions | |||
Applying formula to only NON-EMPTY cells in range | Excel Discussion (Misc queries) | |||
How do I set up a formula to repat 5 consecutive cells in a colum. | Excel Worksheet Functions | |||
Formula not updating | Excel Worksheet Functions |