Results 1 to 12 of 12
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Why are external references absolute? (Excel All)

    Pardon my negligence, but why are external references absolute? If I remove the $ signs and make them relative will this influence variables around the integrity of the link or spreadsheet structure? What are the pro's and con's around external links being default absolute?
    TX
    Regards,
    Rudi

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: Why are external references absolute? (Excel All)

    The pro of external links being absolute by default is you know exactly what you are referring to and getting.
    If, after entering such an external link formula, you happended to say insert a column or 2, you will still be referring to the same external value.
    This is probably what would be required, so it is a good default.

    On the otherhand, changing it to relative can be useful if you are 'copying formulas' and want 'corresponding' data values fetched back from the external link.

    zeddy

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Why are external references absolute? (Excel All)

    Rudi:
    From my own use of Excel, when I use links I much prefer variable links. I have not experienced added problems with variable external links and they seem to operate just as well.

    Recommendation - When dealing with a modest number of external links I prefer to name the cell in the source workbook and use that name as the variable in the target workbook. Also in the Target workbook I change the font color of links to make it easier to spot linked data. Lastly adding a comment to the target cell can make later modifications easier.

    TD

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Why are external references absolute? (Excel All)

    >> If, after entering such an external link formula, you happended to say insert a column or 2, you will still be referring to the same external value.

    Tx for your reply Zeddy. Your statement above is true for relative formulas too?? So I still am not sure why the default is absolute and if it makes a difference somewhere to the calcs???
    Regards,
    Rudi

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Why are external references absolute? (Excel All)

    Hi TD,

    When you use range names to refer to external links, are they referenced as absolute or relative??? Al I really am concerned about if if I make external links relative, will it play some negative role in my WB.

    TX
    Regards,
    Rudi

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: Why are external references absolute? (Excel All)

    Hi Rudi

    Yes you are correct. If you change a formula with an external link from absolute to relative, then inserting a few columns won't change it.
    However, if you copy a formula cell (containing an external link with a relative address) then the external link is also 'adjusted' in the copied cells.
    This can be useful in certain circumstances.

    Personally, I hate all external links with a vengance.
    I prefer to use VBVA automation to 'grab' data ranges from external workbooks and paste them in as valuesinto my current workbook.

    If I must refer to other external workbooks, I tend to use INDIRECT to get the data elements I want, i.e. without having to create specific 'links'.

    zeddy

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Why are external references absolute? (Excel All)

    Relative external references can be used just the same as relative internal references, to make it easier to fill down formulas etc. There are no particular disadvantages.

    My guess is that the default is absolute to give you a visual indication that external references are by their nature more 'absolute' than internal ones. Take the following example:
    - You have workbooks Book1 and Book2, each with one worksheet Sheet1.
    - In cell A1 on Sheet1 in Book1, you have a formula =B3
    - In cell A2 on Sheet1 in Book1, you have a formula =[Book2.xls]Sheet1!B3
    - Now select cell A3 on Sheet1 in Book1 and select Insert | Rows.
    - The formula in A1 now reads =B4
    - Save and close Book1.
    - Select cell A3 on Sheet1 in Book2 and select Insert | Rows.
    - Reopen Book1.
    - The formula in cell A2 still reads =[Book2.xls]Sheet1!B3

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: Why are external references absolute? (Excel All)

    Hi Hans

    My conclusion would be that changing an external link reference from absolute to relative would have no impact in the current book (save for what happens when you fill down formulas etc).

    In your example, if you didn't close book1 and inserted rows in Book1, then in Book2 the external reference would still adjust whether you used absolute or relative addresses in the external link formula.

    zeddy

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Why are external references absolute? (Excel All)

    I agree!

    My remark was not intended to imply that the reference being absolute or relative would make any difference.

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Why are external references absolute? (Excel All)

    Hi,
    That seems like a little more work. I am not to phased with external links. Personally I find that if you manage them with the Links dialog, they are pretty easy to maintain. And the improvements to the XP and 2003 version of the Links dialog is great, esp. that Status column!

    Tx for you input and suggestions
    Cheers
    Regards,
    Rudi

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Why are external references absolute? (Excel All)

    Tx for the example Hans. Having followed the steps, I see that the internal formula updates to B4, but I also notice that the external ref stays B3 (wether the reference was absolute or relative). So at the end of it all, there is no difference except for when autofilling or copying rwefs to other locations.
    TX
    Regards,
    Rudi

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why are external references absolute? (Excel All)

    <hr>Yes you are correct. If you change a formula with an external link from absolute to relative, then inserting a few columns won't change it.<hr>

    This is not entirely correct. There are two options.

    1. The source book is open

    Any changes in the source's books layout are reflected in the target workbook's cell references to the source workbook

    2. The source book is NOT open

    Any changes in the source's books layout are NOT reflected in the target workbook's cell references to the source workbook

    Both are regardless of whether you're using $ or not.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •