How do I keep my cell reference from moving even when I use the $.

[Guest]

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?????

 

[Guest]

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.

 

[Dave Peterson]

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")