MrExcel.com - Your One Stop for Excel Tips & Solutions

Get Data From Another Worksheet By Using Indirect

February 12, 2021 - by

Get Data From Another Worksheet By Using Indirect

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 #REF! error.

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.

Figure 32. Using INDIRECT based on a date doesn’t seem to work.
Figure 32. Using INDIRECT based on a date doesn’t seem to work.

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.

Figure 33. The reference fails because Excel changes the date back to a serial number.
Figure 33. The reference fails because Excel changes the date back to a serial number.

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.

Figure 34. You use the TEXT function to convert the date to text that looks like a date.
Figure 34. You use the TEXT function to convert the date to text that looks like a date.

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:

=INDIRECT("'"&TEXT(A3, "mmm-d")&"'!B4")

If your worksheets are named 9-1, the formula would be:

=INDIRECT("'"&TEXT(A3, "m-d")&"'!B4")

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.

Figure 35. In this case, TEXT can be replaced with DAY.
Figure 35. In this case, TEXT can be replaced with DAY.

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.