Are you tired of manually linking similar outputs from different tabs in an Excel model to a summary sheet? Many of you will have spent time doing this, wishing for a magic formula to complete the task quickly and accurately. I have some good news for you…one actually exists!
You can easily use “=INDIRECT” to create an automatic output sheet where you know the cell references on the source tabs that you want to link to.
How to link cells in Excel
E.g In the example below we want to link the contents of cells B12 and B13 to an output summary.
Expert tip – it is quicker and easier if each source tab has a similar layout so you are linking to the same cells on each of the source tabs.
Excel summary sheet
This must include both the tab names (company names) and the cell references from the other tabs that you want to link across to.
Write the formula
The formula is split into 4 components:
- Specify the indirect function
- Identify which sheet you want to link to
- Add an exclamation mark “!” which identifies that this is a cross sheet formula
- Add the reference of the cell that contains the cell reference from the source sheet
In the example the formula would be: =INDIRECT(A2&”!”&B6)
Expert tip – Make sure the formula is copyable by using “$” to lock in the column letter for the company name and the row number for the reference cells: =INDIRECT($A2&”!”&B$6)
A word of warning
Since you are specifying that you want to link to a specific cell on the source sheets, this means that you cannot insert or remove rows or columns on any of the source sheets or you will then no longer be linking to the correct cell. e.g. a row has been removed:
The summary sheet is now linking to the wrong cells:
Please do not hesitate to contact us, if you are having trouble viewing or accessing this article.
Copyright© 2016 AMT Training
