If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
!REF problems
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 |
#2
|
|||
|
|||
!REF problems
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. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Donald Bucci" wrote in message ... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 |
#3
|
|||
|
|||
!REF problems
Thank you, Ken. This is exactly what I was looking for.
Don -----Original Message----- 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. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------- ------------------ It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------- ------------------ "Donald Bucci" wrote in message ... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 . |
#4
|
|||
|
|||
!REF problems
My pleasure :-)
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- snip --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 |
Thread Tools | |
Display Modes | |
|
|