Note: the external workbook must be open for this reference to work. Note the square brackets ([ ]) around workbook name, single quotes (’ ‘) around the worksheet + sheet, and the exclamation mark (!) that follows. To create a reference like this using text, we use concatenation to join values from columns B, C, and D with the required brackets, quotes, and exclamation mark: The result is fed into INDIRECT as ref_text. Once the concatenation is performed, we have: The INDIRECT function then evaluates the text and converts it to a genuine reference, and Excel follows the reference and returns the value at the given reference. Note: if the reference is invalid, or if the workbook referenced is not open, INDIRECT will throw a #REF error. You can catch this error with the IFERROR function and display a custom result if you like.
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.
