The excel INDIRECT function can convert a text string to a valid reference. For example, INDIRECT(“A1”) will return a reference to cell A1, and INDIRECT(“Scores”) will return a reference to a named range ( suppose Scores is the range object name ). This article will tell you how to use the excel INDIRECT function with examples.
1. How To Use Excel INDIRECT Function.
- The excel INDIRECT function contains 2 arguments ref_text and [a1].
- The ref_text is a text string, it is the referenced excel object’s name such as “A1” or “Scores”.
- The text “A1” is cell A1‘s name, and the text “Scores” is the named range object’s name in this example ( How To Define Named Constant In Excel And Create Excel Named Range ).
- The argument a1 is optional, it is a boolean value to indicate the reference type of the ref_text value.
- The a1 argument’s default value is True, which means ref_text will use an A1-style reference.
- If you set the a1 argument’s value to False, then it will use R1C1-style to return the ref_text referenced object.
2. Why Need Excel INDIRECT Function?
- You can get excel object references by combining multiple strings from different cells.
- You can use a fixed cell as a variable to store different object names ( such as worksheet names ), and then use the INDIRECT function to reference the different worksheets.
- In one word, using the excel INDIRECT function, you can get different excel objects dynamically.
3. How To Use Excel Indirect Function Examples.
3.1 Use Excel Indirect Function To Reference Cells In Different Worksheets.
- Below are the data cells in this example, the data cell’s worksheet name is ‘Excel Functions’ which is saved in cell A8.
- And the cell name which we want to reference is ‘B2’ in this worksheet which is saved in cell B8.
A B C 1 … … … 2 Excel … … … 7 WorkSheet Name Cell Name Cell Value 8 Excel Functions B2 Excel
- So we input the formula =INDIRECT(“‘”&A8&”‘”&”!”&B8) in cell C8 and press the enter key, then it will display the text Excel in cell C8.
- The text “‘”&A8&”‘”&”!”&B8 will be translated to “‘”&”Excel Functions”&”‘”&”!”&”B2″, and then be translated to “‘Excel Functions’!B2”.
- Then the formula =INDIRECT(“‘”&A8&”‘”&”!”&B8) will be translated to =INDIRECT(“‘Excel Functions’!B2”).
- Then the above formula will return the reference to cell B2 in the worksheet ‘Excel Functions’.
- So that it will insert the cell B2‘s value ( Excel ) located in the worksheet ‘Excel Functions’ into cell C8.
- Because the worksheet name contains white space, then we should use 2 single quotes to wrap the worksheet name in the formula.
3.2 Use Excel Indirect Function To Reference Different Cells Dynamically.
- Suppose there is a drop-down list in cell A1 (How To Add Drop-Down List In Excel Cell).
- The drop-down list contains 3 items B1, B2, and B3.
- Cell B1‘s value is Excel, cell B2‘s value is Word, and cell B3‘s value is PPT.
- Input the formula =INDIRECT(A1) in cell C1.
- Now when you select a different list item from the list in cell A1.
- You will find the text in cell C1 is also changed accordingly.
- Below is the example worksheet image.
- In this example, the formula =INDIRECT(A1) will return=INDIRECT(“B1”), =INDIRECT(“B2”), or=INDIRECT(“B3”).
- And the formula =INDIRECT(“B1”) will return =B1 when the user selects B1 from the list in cell A1.
- Then cell C1‘s value will be cell B1‘s value which is Excel.