Transcripts for the above video clip:

LINKED SHEETS PROJECT

 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 an error 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.