Millionaires and their assets..can you link it up?

Close your eyes and imagine you're a multi-millionaire. Think of all those houses, holiday homes, cars, private jets.... When I do this, I just think that it must be an absolute nightmare to keep track of the asset values. (Ok, I may fantasise about a beach house in the Maldives...but I'd still worry about keeping my asset values under control!)

Back to your daydreams....now picture that your beloved wife blindsides you with a surprise divorce (who could I be referring to ?). Besides maybe needing a good friend on speed dial, you would also need to have those asset values to hand, and pretty darn quick. Imagine, too, that you don't mind having a large staff, and you don't want any one of your accountants having access to too many of your assets. To provide adequate segregation, you have an accountant deal with each of your asset groups, and each of them report to you directly with a summary of assets, values, and the current month's running cost.

* Note - this is a simple illustration, so these figures are completely and utterly fictional, as I have eschewed buying luxury houses or cars in lieu of a simple life. Also, I would assume any multi-millionaire worth his salt would want to know a whole lot more about his assets, but we're keeping this simple....

So how can I help speed up this little part of your working life? Well, imagine each asset accountant has a template which he updates each month:

You, sitting in your plush office, add these figures every month to an asset overview sheet you use to keep track of your investments. This admittedly wouldn't take long but, to make it even easier, you could link your workbooks so that when your accountants update their templates each month, yours would automatically update too.

So, how do we link workbooks? Easy really. There are 3 ways to choose from, which I will take you through below, but what you want is for your summary to have formulae that look like this:

All this formula is saying is, instead of looking for the total on the summary sheet, go to a different workbook (City Apartments.xlsx) and in that workbook, look for a sheet called "City Apartments", and then find cell C10 on that sheet and bring its contents back to the summary sheet.

The official syntax is [WorkbookName]SheetName!CellAddress

So, how do get the Linking Formula into your workbook?

1) You can type it in.
This may be an easier option if you don't have the other file open, but some things to note on typing in a file name:

 a) 100% accuracy is required otherwise it won't work;

 b) If the file is closed, you will need to type in the full address, which may look something like this; ='C\Data\Assets\Monthly Updates\[City Apartments.xlsx]City Apartments'!$C$10 . Not so easy to get 100% right (see point a);

 c) If your workbook or worksheet has spaces, it will need the little ' at the beginning and end of the name.

2) You can point and click
This really is as easy as it sounds. Firstly, open your Summary Sheet and City Apartments sheet. In the Summary Sheet, click on Cell C4 and press =. Then go to your City Apartments sheet and click on cell C10. Press enter and your formula is there, perfectly spelt and looking impressive.

You can use this for all sorts of formulae that require an external link, not just picking up a number. You just type your formula and when you need the external data, just include the external cell references as I described above.

This, in my mind, is the best and easiest way of setting up links. It allows you the most flexibility, and is always accurate.

3) You can paste a link

If you simply wanted to transfer data from one cell into your workbook, as we did in our example, you can just paste as a link. So, using our example, to transfer the total value from the Cars workbook, you would click on cell C11 in the Cars workbook and copy it. Then go to cell C6 in the summary sheet, and Paste Special - Paste Link (Or Home - Clipboard - Paste - Paste Link). This creates the same linked formula you would get by pointing and clicking.

With points 2 and 3, when you close the external workbook, the full name is then shown in your formula.

With all of the above steps, if the external file is updated, when you open the summary file, the data will update automatically.

Also note that in Excel 2010, new security feautures have been added. So the first time you re-open your Summary sheet, you will see a ribbon appearing at the top of your page asking whether you want to enable links. Click Yes, and Excel shouldn't ask you again.

Now, I hate to bring you back down to earth, but time to stop imagining you're a multi-millionaire, and start assessing whether there are any workbooks you can link, and therefore save you time.

Our newsletter this week will provide some more tips on link, and some troubleshooting hints. If you haven't already, why not sign up here?

Happy Spreadsheeting!

A PS here (thanks to reader Martin for pointing out that I forgot to mention this) - if you change the source file (ie - insert or delete rows or columns) , when your final file is closed, the final file will not be adjusted, and will pick up the wrong data. So either always have both files open when making adjustments, or use a vlookup to pick up your data.