Note: The point of INDIRECT here is to build a formula where the sheet name is a dynamic variable. For example, you could change a sheet name (perhaps with a drop down menu) and pull in information from different worksheet. In this example, we have Sheet names in column B, so we join the sheet name to the cell reference A1 using concatenation: After concatenation, we have: INDIRECT recognizes this as a valid reference to cell A1 in Sheet1, and returns the value in A1, 100. In cell C7, the formula evaluates like this: And so on, for each formula in column C.
Space and punctuation in sheet names
If sheet names contain spaces, or punctuation characters, you’ll need to adjust the formula to wrap the sheet name in single quotes (’) like this: where sheet_name is a reference that contains the sheet name. For the example on this page, the formula would be: Note this requirement is not specific to the INDIRECT function. Any formula that refers to a sheet name with space or punctuation must enclose the sheet name in single quotes.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
