Why are external references absolute? (Excel All)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

>> 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???

Reply | Quote

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

Reply | Quote

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

Reply | Quote

[indent]

---

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.

---

[/indent]

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

I agree!

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

Reply | Quote

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

Reply | Quote