The INDIRECT function then evaluates the text and converts it to a reference. 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.
To create a formula with a dynamic sheet name you can use the INDIRECT function. In the example shown, the formula in C6 is: = INDIRECT ( B6 & "!A1" ) Note: The point of this approach is it lets you to build a formula where the sheet...
To lookup product data, pricing, or other information stored in a separate (external) workbook, you can use the VLOOKUP function with a full reference to the other workbook. In the example shown, the formula in C5 is: = VLOOKUP ( B5 , ' [ product...
The Excel INDIRECT function returns a valid reference from a given text string. Use INDIRECT when you need to convert a reference assembled as text into a proper reference.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.