Get Data From Another Worksheet By Using Indirect
February 12, 2021 - by Bill Jelen
Challenge: You have 31 daily worksheets in a workbook, 1 for each day of the month. A cell on the summary worksheet contains a date. You want to use the date cell in
INDIRECT to grab data from a certain day’s worksheet, but the formula always returns a
In Figure 32, cell E3 contains a date. You’ve used the custom number format MMM D YYYY to ensure that the date in E3 looks like the worksheet name. The formula returns an error.
Background: No matter how you format cell E3, Excel converts the date back to a serial number when it is used in
INDIRECT. You might hope for a reference like ‘Sep 1 2008’!B4 but instead get =‘39692’!B4. Figure 33 shows the formula after you use Evaluate Formula. 39692 is how Excel actually stores the date September 1, 2008, on a Windows PC.
Solution: You need to specify the correct custom number format by using the
TEXT function. In Figure 34, the formula is
=INDIRECT (“’”&TEXT (A3, “mmm d yyyy” ) &“’!B4”) . This builds a reference such as ‘Sep 1 2008’!B4.
The second argument of the
TEXT function coerces the date to match the style of the worksheet name. If someone built a worksheet with a name such as Sep-1, the formula would be:
If your worksheets are named 9-1, the formula would be:
If you are lucky enough that your worksheets are simply named 1, 2, 3, and so on, you can use
=INDIRECT(DAY(A3)&"!B4"), as shown in Figure 35.
Gotcha: Formulas built with
INDIRECT are particularly susceptible to generating
#REF! errors if someone changes a worksheet name. Say that you have the =3!B4. If you change the name of the worksheet from 3 to Sep 3, the formula automatically changes to =‘Sep 3’!B4. However, when you start using
INDIRECT, the formula fails when someone changes the name of the worksheet. If you use
INDIRECT, you need to convince people not to change the worksheet names or protect the workbook.
Summary: A date used in
INDIRECT always changes back to the date serial number. You need to use other functions to force the date to appear in the proper format.
Title Photo: Zander Janzen van Rensburg at Unsplash.com
This article is an excerpt from Excel Gurus Gone Wild.