Cells no longer linked after inserts

[SF]

I linked several worksheets with a mastersheet and the info updates fine,
until I insert a new column. Some of the linked cell formula change to
incorporate the new postition on the mastersheet, but others don't. I end up
having to change the formula so they can match the new position. Is there
anyway I can stop this from happening so that when the originals move
position the linked cells continue to reflect the correct info.

 

[Dave Peterson]

Are these worksheets in a single workbook?

If yes, then I've never seen this with "normal" formulas. You may want to share
the formula that doesn't update when you make these changes.

If no, then you could open all the workbooks (in the same instance of excel)
that are linked and make the changes. Excel will update the formulas to reflect
your changes. Remember to save all the files so that those changes excel made
are saved.

If you only have a few cells that you're retrieving, you could keep that file
with the links (the "receiving" file) closed, but use a named range
(insert|Name|Define). Then your formula would use that name instead of an
address.

 

[SF]

Everything is in the same workbook. I use the formula =mastersheet!A1 (just
an example) to update all the other list, but there are times where the list
will keep the original formula and not change to incorporate the new position
of the original. I hate having to keep going back to figure out which cells
are no longer reflecting the right information.

 

[Dave Peterson]

Inserting/deleting rows/columns won't break these formulas.

They'll adjust to point to whereever that A1 cell got moved to.

On the other hand, if you wanted the formula to always refer to A1 no matter
what you inserted, you could use:

=indirect("mastersheet!a1")

=====
But my guess is that you're sorting this mastersheet and excel won't help you
here.

I'd try to add a unique key that I can match up on and retrieve information
based on that key.

Debra Dalgleish has lots of notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

 

[SF]

So what your saying is that if I insert a column the formula shouldn't
change, but if I try to sort the mastersheet, the formula will not follow
suit...?

 

[Dave Peterson]

If you have a simple formula like this in A1 of Sheet1:
=mastersheet!a99

Then if you sort the mastersheet, your formula in A1 of Sheet1 will still look
like:
=mastersheet!a99

And unless you're very, very lucky, the value in A99 in the mastersheet will be
different.

And I'm saying that if you have a formula like:
=mastersheet!c9
and insert a new column A, then your formula will get adjusted to:
=mastersheet!d9

So what your saying is that if I insert a column the formula shouldn't
change, but if I try to sort the mastersheet, the formula will not follow
suit...?

 

[Dave Peterson]

Added a couple of words just to clarify...

And I'm saying that if you have a formula like:
=mastersheet!c9
and insert a new column A *in the mastersheet*, then your formula will get
adjusted to:
=mastersheet!d9