Microsoft Office Tutorials and References
In Depth Information
GET DATA FROM ANOTHER WORKSHEET BY USING INDIRECT
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.
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. h e reference fails because Excel changes the date back
to a serial number.