Excel: INDIRECT (Part 1)

INDIRECT

This function in Excel returns a valid cell reference from a given text string. INDIRECT is useful when you want to assemble a text value that can be used as a valid reference. INDIRECT is useful when you need to build a text value by concatenating separate text strings that can then be interpreted as a valid cell reference. Sheet names that contain punctuation or space must be enclosed in single quotes ('), as explained in this video. This is not specific to the INDIRECT function; the same limitation is true in all formulas. INDIRECT takes two arguments, ref_text and a1. Ref_text is the text string to evaluate as a reference. A1 indicates the reference style for the incoming text value. When a1 is TRUE (the default value), the style is "A1". When a1 is FALSE, the style is "R1C1". The function is dynamic in that responds to the values in column D. In other words, if a different sheet name is entered in column D9, the value from cell A1 in the new sheet is returned. With the same approach, you could allow a user to select a sheet name with a dropdown list, then construct a reference to the selected sheet with INDIRECT.