maintain references when target cell moves?

[William DeLeo]

Greetings,

I have some cell formulae on sheet2 refering to cells on sheet1.
want the formulae on sheet2 to _never_ change, no matter what happen
on sheet1.

I am finding that when I "move" cells on sheet1, the references o
sheet2 reflect that move ... with both relative and absolut
designations. I also tried locking sheet2 to no avail.

For example: I want "=sheet1!A1" to always point to top left cell o
sheet1, no matter what happens to the cells of sheet1.

So, how can I keep the formulae from changing???

Thanks in advance,
W

 

[Dave O]

The only way I can think of to do this would be to highlight Sheet2,
and perform a search and replace. Search for the = sign and replace it
with some text string such as your initials. This would convert any
formulas to static text strings. You could then move whatever items on
Sheet1 without affecting the cell references on Sheet2. When your
moves are complete, reverse the search and replace: search for your
initials, and replace with the = sign to convert everything back to
dynamic references.

 

[William DeLeo]

OMG!!! Are you serious? I can't believe there isn't a way to not hav
to do that. I definitely thank you for your reply, which confirms m
fears, but I really didn't want my fears confirmed .... I was hoping i
just came down to ignorance on my part.

I'm trying to make this idiot proof. If I were the one using it,
would simply not move anything. But I can't say what others will do.

Damn. Flawed logic from the start it seems ...

Thanks again for your time Dave.
Bill

 

[Guest]

Use the INDIRECT function. That's what it was made for

=INDIRECT("sheet1!A1")

 

[William DeLeo]

!!!

Thanks! I'll look into that.

 

[Dave Peterson]

=indirect("sheet1!a1")

Will always point to A1 on sheet1 as long as there's a sheet1.

 

[William DeLeo]

thank you