Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
inserting rows without adjusting formula relationships
I currently have a spread sheet that contains many rows of measurements. The
measurements are essentially all the same, taken over and over again for an average. I have the spreadsheet set up to calculate the required numbers automatically, using some "IF" statements to accuont for different situations. if the user only uses the spreadsheet as is it works well, but one situation is when a measurement is taken twice, but counted as the same (Averaged). To do this the user should be able to add a row and drag the formulas down where needed. When I do this, though, the referencing gets messed up. for example, if there is a reference from cell A1 to cell A2, when I add a row between them cell A1 changes the reference to A3, instead of leaving it as A2. I could add $'s to preserve the cell numbers, but then that causes problems when a user tries to add a new row for a brand new measurement. (maybe they took more than normal and need more rows). same issue when deleting rows if there are more rows than measurements taken. So how do I have cell A1 not compensate for the new or deleted rows? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
inserting rows without adjusting formula relationships
If you want the formula to always reference A2 no matter what, just
substitute INDIRECT("A2") to everywhere that A2 appears in your formula -Simon "Daiv" wrote: I currently have a spread sheet that contains many rows of measurements. The measurements are essentially all the same, taken over and over again for an average. I have the spreadsheet set up to calculate the required numbers automatically, using some "IF" statements to accuont for different situations. if the user only uses the spreadsheet as is it works well, but one situation is when a measurement is taken twice, but counted as the same (Averaged). To do this the user should be able to add a row and drag the formulas down where needed. When I do this, though, the referencing gets messed up. for example, if there is a reference from cell A1 to cell A2, when I add a row between them cell A1 changes the reference to A3, instead of leaving it as A2. I could add $'s to preserve the cell numbers, but then that causes problems when a user tries to add a new row for a brand new measurement. (maybe they took more than normal and need more rows). same issue when deleting rows if there are more rows than measurements taken. So how do I have cell A1 not compensate for the new or deleted rows? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
Getting all rows of data that have a value in a particular column | Excel Discussion (Misc queries) | |||
repeat a formula same column same number of rows apart | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Automatically inserting rows | Excel Worksheet Functions |