!REF problems

  • Thread starter Thread starter Donald Bucci
  • Start date Start date
D

Donald Bucci

If I wish to rearrange columns in a worksheet (Sheet A)
that contains cells to which another worksheet (Sheet B)
makes reference, isn't there a convention of using the $
in the actual cell label within the formula to maintain
the 'link' (e.g. keying $A$1, rather than A!)? And if so,
would I enter the cell references using this convention in
Sheet B only, or in Sheet A, as well?

Thanks -

Don
 
Are you saying you want to maintain the link to a specific cell address, and
that address is not to change, even if the cell itself physically moves to
another address, ie on SheetA you have for example cell A1 with the following
formula in it:-

=Sheet2!$A$3

and you want to say be able to delete Col A in SheetB, or pick it up and drag it
about and still have the formula on SheetA stay as is pointing to =Sheet2!$A$3.
If so then you need to use the INDIRECT function to take a textual reference
that is not physically linked to the target cell and build a link from it. The
textual reference will not change even within the scenarios outlined, so the
INDIRECT function will continue to build the same link, eg replace the formula
above with the following:-

=INDIRECT("Sheet2!$A$3")

The $ signs are pretty much irrelevant in this scenario and can be omitted if
you like.
 
Back
Top