maintain references when target cell moves?
 

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

maintain references when target cell moves?
 

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.

maintain references when target cell moves?
 

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


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

maintain references when target cell moves?
 

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

maintain references when target cell moves?
 

!!!

Thanks! I'll look into that.

:)


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

maintain references when target cell moves?
 

=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

maintain references when target cell moves?
 

thank you!


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