Microsoft Office Tutorials and References
In Depth Information
POINT TO ANOTHER WORKSHEET WITH INDIRECT
Additional Details: The argument for INDIRECT can be calculated on the ﬂ y. In
Figure 27, cell C3 concatenates a column letter from C1 and a row number from C2,
and it returns a value from B8.
Figure 27. Here, the reference is concatenated from values in other cells.
Summary: The INDIRECT function allows you to calculate from where to pull a
POINT TO ANOTHER WORKSHEET
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:
If the worksheet name contains no spaces, you can leave out the
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.