maintain references when target cell moves?

[William DeLeo]

Greetings,

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

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

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

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

Thanks in advance,
WD

[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 have to do that. I definitely thank you for your reply, which confirms my fears, but I really didn't want my fears confirmed .... I was hoping it just came down to ignorance on my part.

I'm trying to make this idiot proof. If I were the one using it, I 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.
Billy

[Sloth]

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

=INDIRECT("sheet1!A1")

"William DeLeo" wrote:

>
> Greetings,
>
> I have some cell formulae on sheet2 refering to cells on sheet1. I
> want the formulae on sheet2 to _never_ change, no matter what happens
> on sheet1.
>
> I am finding that when I "move" cells on sheet1, the references on
> sheet2 reflect that move ... with both relative and absolute
> designations. I also tried locking sheet2 to no avail.
>
> For example: I want "=sheet1!A1" to always point to top left cell of
> sheet1, no matter what happens to the cells of sheet1.
>
> So, how can I keep the formulae from changing???
>
> Thanks in advance,
> WD
>
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile: http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=518274
>
>

[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 wrote:
>
> Greetings,
>
> I have some cell formulae on sheet2 refering to cells on sheet1. I
> want the formulae on sheet2 to _never_ change, no matter what happens
> on sheet1.
>
> I am finding that when I "move" cells on sheet1, the references on
> sheet2 reflect that move ... with both relative and absolute
> designations. I also tried locking sheet2 to no avail.
>
> For example: I want "=sheet1!A1" to always point to top left cell of
> sheet1, no matter what happens to the cells of sheet1.
>
> So, how can I keep the formulae from changing???
>
> Thanks in advance,
> WD
>
> --
> William DeLeo
> ------------------------------------------------------------------------
> William DeLeo's Profile: http://www.excelforum.com/member.php...fo&userid=1256
> View this thread: http://www.excelforum.com/showthread...hreadid=518274

--

Dave Peterson

[William DeLeo]

thank you!