Referencing Cells in Other Worksheets and Workbooks using the INDIRECT function

When working with multiple worksheets or tabs, it becomes useful to reference them in formulas or to consolidate data. First, I’ll look at working with different tabs.

The easiest way to refer to a cell in another tab is to start a formula with the = sign, and then click over to the other tab and click on the cell you want to reference.  In the following example I am typing a formula in tab A but will reference cell B6 on tab B.

Note the reference in the formula bar:

=B!B6

The tab is denoted by the ! after the tab name. If I wanted to reference tab A it would look as follows:

=A!B6

But what if my tab wasn’t all one word, and the tab was named A and B? In this case, I would need to use apostrophes:

=’A and B’!B6

So to summarize, the naming convention for referencing another sheet is:

SHEET1!A1 or ’SHEET 1’!A1

Now let’s assume the cell you want to reference isn’t in this same workbook, then the reference looks a bit different. If the workbook is open:

The key difference is you are adding the workbookname. This method will work if the workbook is open, but not recommended if you are referencing a workbook that is closed because Excel might not know which workbook you want to reference and cause errors. To properly reference a closed workbook, use the following:

The above formula will work if the workbook is saved on my desktop.

In this example I only used a single cell, but you can just as easily reference a range. Change A1 to A1:A10 and you are referencing a range instead of a cell. This comes in useful if you want to use the reference in a lookup.

Referencing other worksheets and tabs is not difficult once you get used to the syntax, but if you had dozens of tabs or workbooks you wanted to reference, it might get tiresome to keep doing this. That’s where the INDIRECT function comes in handy. You can use formulas to populate these cell references so that you don’t have to re-type them or even use find and replace.

Imagine you have sales data on multiple tabs. Each tab represents a year. So if I want to summarize data from five different years (tabs), that’s five different references I have to use. Or I can use the INDIRECT function. The way the formula works is you can enter the entire location of the cell that you want to reference. The benefit is being able to use relative and absolute references in place of having to type out the full address over and over again.

Here is a comparison of how the references would look using each method:

In the indirect function, I am able to use a relative reference for the year. If I enter the formula in cell B4, all I have to do is copy the formula down and it will reference all the other years(tabs) without having to re-type the full location. The benefit here is apparent when you are dealing with many different tabs. In column C I am unable to do this and have to re-key each cell individually.

The tricky part of using the indirect function is making sure you are correctly combining the ampersands, variables, and constants. The ampersand you use to join a constant and a variable within the INDIRECT formula. In this example, the variable is the year (tab name). The constant is the syntax (!) and the cell reference (A1), since their values will never change.  Never put the variable in quotations, only constants. 

Here is a breakdown of the logic of the formula in cell B4:

Because I am referencing another tab (not another workbook), I need to start with the worksheet name. Since I have a variable for the worksheet name, I start with the reference to cell A4, which contains the name of my worksheet: 

=INDIRECT(A4

The remainder of the formula is going to be !A1, or, the constants. To add the constants, I need to add the ampersand, open quotations, enter the constants, and close the quotations and the formula:

=INDIRECT(A4&”!A1”)

You can alternate between constant and variable as much as you like but they have to be linked by an ampersand. You also don’t need to begin the INDIRECT function with a variable, it can be a constant.

You can use the INDIRECT function to reference other workbooks, but unfortunately a limitation of the formula is that it is unable to get data from a closed workbook. So unless you plan to have the workbooks open, the formula will not be of much help here. The formula is most helpful when dealing with multiple tabs in a single workbook.

Some key things to remember when referencing other sheets or workbooks: