Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I keep my cell reference from moving even when I use the $.
Cell on sheet 2 has formula where if false displays a cell value on sheet 1,
i have the $ lock symbols placed but when I insert columns on sheet 1 the reference on sheet 2 increments by how ever many columns I insert. how can I keep this from happening????? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I keep my cell reference from moving even when I use the $.
Mat,
This doesn't answer your question but tells you why this is happening - the formula in sheet 2 has a formula with an absolute reference to a cell in sheet 1. If you insert columns, Excel is "clever" enough to know that the cell you were referring to has moved so it updates the formula accordingly. - I would have thought that this is what you wanted to see happening unless for some reason you don't need an absolute reference e.g. Say cell A1 in sheet 1 has a tax rate & cell A1 in sheet 2 refers to cell A1 in sheet 1 i.e. Sales * Sheet1! A1. If you inserted a columns in sheet 1, why would you not want the formula to update in sheet 2 to refer to the new location of the cell containing the tax rate? Doesn;t make sense I'm afraid. "Mat_W" wrote: Cell on sheet 2 has formula where if false displays a cell value on sheet 1, i have the $ lock symbols placed but when I insert columns on sheet 1 the reference on sheet 2 increments by how ever many columns I insert. how can I keep this from happening????? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I keep my cell reference from moving even when I use the $.
If you always want to refer to A1 on Sheet1--no matter if there are any
rows/columns deleted/inserted, you could use: =indirect("'Sheet1'!a1") Mat_W wrote: Cell on sheet 2 has formula where if false displays a cell value on sheet 1, i have the $ lock symbols placed but when I insert columns on sheet 1 the reference on sheet 2 increments by how ever many columns I insert. how can I keep this from happening????? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving left of a cell reference | Excel Worksheet Functions | |||
Moving left of a cell reference | Excel Worksheet Functions | |||
Moving Cell Reference after Sorting | New Users to Excel | |||
Possible Lookup Table | Excel Worksheet Functions | |||
how to create a variable column in cell reference | Excel Worksheet Functions |