How to avoid External Link Errors in Excel using the EDIT LINKS tool and what the risks are
External Link Errors in Excel
Linking cells in two different spreadsheets is one of Excel’s best features. It is used extensively in the business world. However what people don’t realize is how easy it is to make External Link Errors in Excel and how important it is that all linked spreadsheets are open at the same time.
So for example you’ve got two spreadsheets here. The one is just giving you the Designation of the people involved and their Rate per hour, the second one is actually the Calculation of the cost. So what you’ll see here is that that formula is taking the number of hours and multiplying it by the relevant rate, which is correctly listed there. Similarly the Structural Engineer taking how many hours he’s done, and there looking at his rate.
Now if you keep these two workbooks open at the same time and make structural changes, the effect is that Excel will adapt a formula to take that in to account. So just to emphasize
this formula here took D12
and multiplied it here by G12, which is here
if for whatever reason we insert another row here,
what you’ll see is if you go back to this formula, Excel has adapted the formula to take that movement in to account
As a result you are still correctly looking at the correct place; however what people don’t understand is that it’s vital that the spreadsheet itself is open, so for example
lets close this spreadsheet down
and we’ll make the changes to it
and then here for whatever reason well come and while that other spreadsheet is closed down,
insert another row,
now, if we open up that spreadsheet again,
what you’ll see and it’s immediately notice here is that suddenly there’s a zeros coming through
The reason for this, it is still looking at the corrections made for the first row entry. So if when you look at where it’s looking it’s still looking at that old cell.
This is a very common error to make and if the spreadsheets get big enough its very difficult to spot, and what is more risky is if it actually doesn’t look at a blank it looks at a another number, if for whatever reason you’ve included a number in that cell. Again a very common error to make.
What needs to be understood here is that if you’re going to have linked workbooks, you cannot make structural changes without opening all the linked workbooks so that Excel can adapt them.
If your workbooks are too big to open up, it is vital that you make sure that everyone is clear that structural changes, which is inserting rows and inserting columns cannot happen, or must be strictly strictly controlled.