Point To Another Worksheet With Indirect

February 10, 2021 - by Bill Jelen

Challenge: You need to grab cell B4 from one of many worksheets. You have to determine which worksheet, based on a cell label or a calculation, and you’re wondering if INDIRECT can point to another worksheet.

Setup: INDIRECT can point to another worksheet. However, it requires special handling if the worksheet name contains spaces or a date.

If a worksheet has a space in the name, you must build the reference to the worksheet by using apostrophes around the worksheet name, followed by an exclamation point and then the cell address. For example:

='Income Statement'!B2

If the worksheet name contains no spaces, you can leave out the apostrophes:

=Revenue!B2

However, if you have a mix of worksheet names that may or may not contain a space, you might as well plan on including the apostrophes.

Solution: Figure 28 shows six branch worksheets. Each has data in column B. The goal is to write a formula in row 4 that will pull data from column B of the various worksheets, based on the labels in row 3. The formula in cell G4 in Figure 28 works fine for row 4:

Note: This formula includes the extra hassle of using apostrophes, even though only one of the worksheets, Eden Prairie, contains a space.

However, the problem with the formula in G4 is that it is hard-coded to grab data from B4, so it cannot automatically copy to rows 5 through 7. To allow the formula to grab data from other rows, you can use the CELL function or the ADDRESS function. Both of these methods work. You can be up and running using CELL in a matter of seconds, but ADDRESS might ultimately be easier, once you understand the nuances of using it.

=CELL(“address”,$B4) returns the text $B$4

This is perfect for inserting in the INDIRECT function. The dollar sign before the B makes sure that the formula points to column B on each worksheet. The lack of a dollar sign before the 4 in $B4 allows the formula to point to row 5, 6, 7, and so on as you copy down. In Figure 29, the formula in cell C5 is:

=INDIRECT("'"&C$3&"'!"&CELL("address",$B5))

This formula can be copied throughout the table.

You can use the ADDRESS function instead of the CELL function. In its simplest form, =ADDRESS (Row, Column) returns a cell address. For example, =ADDRESS (5, 2) returns the text $B$5. Initially, it might seem more complex to write =ADDRESS(ROW(),2) instead of using CELL in order to refer to column B in the current row. However, ADDRESS offers three additional optional arguments:

Note: The third and fourth arguments do not help you in this topic, but you have to learn them so that you can get to the fifth argument.

• The third argument controls whether and where dollar signs appear in the address. Here is an easy way to remember how this argument works:. The number in the argument corresponds to how many times you press the F4 key to achieve the combination of dollar signs:
  • » =ADDRESS(5,2,1) gives you $B$5.
  • » =ADDRESS(5,2,2) locks only the row (B$5).
  • » =ADDRESS(5,2,3) locks only the column ($B5).
  • » =ADDRESS(5,2,4) locks nothing (=B5).
• The fourth argument controls whether you get an A1-style reference or an R1 C1-reference:
  • » =ADDRESS(5,2,1,1) return the A1-style reference $B$5.
  • » =ADDRESS(5,2,1,0) return the R1C1 reference R5C2.
• The fifth argument can accept a sheet name. In this case, Microsoft examines the sheet name and figures out whether you need apostrophes:
  • » =ADDRESS(5,2,4,1,"Atlanta") returns Atlanta!B5.
  • » =ADDRESS(5,2,4,1,"Eden Prairie") returns 'Eden Prairie'!B5.

Note: You don’t really have to remember how the third and fourth arguments work. If you simply use =ADDRESS (5, 2, , , “Atlanta” ) , Excel returns Atlanta!$B$5.

The version of ADDRESS with the fifth argument returns text that can be used in the INDIRECT function. In Figure 30, the formula in cell D6 is:

=INDIRECT(ADDRESS(ROW(), 2, 4, 1, D$3))

The first argument in ADDRESS is ROW(), which ensures that Excel grabs the row where the formula is. The second argument is hard-coded to a 2 to make sure you always get column B. The third and fourth arguments return a relative A1-style reference. The fifth argument contains only a dollar sign before the row to make sure you always get a sheet name from row 3, but the column can change as the formula is copied.

Additional Details: There is a version of INDIRECT that works with R1C1-style references because there are times when using R1C1 is actually easier. So far, all the examples of INDIRECT have used only a single argument. Leaving off the second argument:

=INDIRECT("B4")

or specifying TRUE as the second argument:

=INDIRECT("B4", TRUE)

tells Excel to interpret the reference as an A1-style reference. Using FALSE as the second argument:

=INDIRECT("RC", False)

tells Excel to interpret the reference as an R1C1 reference.

The reference =RC points to the current row and the current column. Including a number after the R or C creates an absolute reference to a particular row or column. =RC2 is the R1C1 method for referring to column B of this row. If you use R1C1, you don’t have to worry about using CELL or ADDRESS. In Figure 31, the formula used in C7 is:

=INDIRECT("'"&C$3&"'!RC2", FALSE)

This formula can be copied throughout the table. Note that you do not have to switch the worksheet to R1C1 style in order to use this formula.

Summary: With a little extra thought, you can use INDIRECT to point to another worksheet.

Title Photo: Tyler Delgado at Unsplash.com

This article is an excerpt from Excel Gurus Gone Wild.