Excel references change when importing data
 

This issue is similar to absolute/relative references, but it's related
to the data being referenced, not the cells doing the referencing. If
I have a call the references cell A3 then I insert a row above row 3
(making the original A3 cell now A4), my reference automatically
changes to A4. How can I get it to stay at A3 no matter what happens?

This may sound like an odd request, but I reference a bunch of cells on
a worksheet. This sheet retrieves data from a database which changes
regularly. The changes that it receives are not just updated in the
cells, but rather the rows are deleted and reinserted changing all my
references to this sheet. For example, say I import data and it fills
cells A1 to A20 and I make a reference to A15. If I change the data
that is imported and the data now only fills cells A1 to A10, the
reference to A15 is now set to #REF since it actually clears all cells
from A1 to A20 and then reinserts new data. Any ideas on what may be
going on and how I can fix it? Thanks in advance!


--
jimithing1980
------------------------------------------------------------------------
jimithing1980's Profile: http://www.excelforum.com/member.php...o&userid=36442
View this thread: http://www.excelforum.com/showthread...hreadid=562067

Excel references change when importing data
 

=indirect("a3")

will always point at A3--if you insert/delete rows or columns.

jimithing1980 wrote:

This issue is similar to absolute/relative references, but it's related
to the data being referenced, not the cells doing the referencing. If
I have a call the references cell A3 then I insert a row above row 3
(making the original A3 cell now A4), my reference automatically
changes to A4. How can I get it to stay at A3 no matter what happens?

This may sound like an odd request, but I reference a bunch of cells on
a worksheet. This sheet retrieves data from a database which changes
regularly. The changes that it receives are not just updated in the
cells, but rather the rows are deleted and reinserted changing all my
references to this sheet. For example, say I import data and it fills
cells A1 to A20 and I make a reference to A15. If I change the data
that is imported and the data now only fills cells A1 to A10, the
reference to A15 is now set to #REF since it actually clears all cells
from A1 to A20 and then reinserts new data. Any ideas on what may be
going on and how I can fix it? Thanks in advance!

--
jimithing1980
------------------------------------------------------------------------
jimithing1980's Profile: http://www.excelforum.com/member.php...o&userid=36442
View this thread: http://www.excelforum.com/showthread...hreadid=562067

--

Dave Peterson