We are now in Sheet 1 of the same workbook. To create an indirect reference to Cell A1 from Sheet2:
- Activate a cell (Cell A2) and write the sheet reference below:
- In another cell, write the INDIRECT function as follows:
= INDIRECT (A2)
- Hit Enter and you have your indirect reference to another sheet all set ✌
We have the value from cell A1 of Sheet 2 in Sheet 1. Yay!
If you find it hard to write the cell reference from another sheet like that, you may use the CONCATENATE function as below:
- Write the name of the sheet and cell reference in different cells. And merge them with an exclamation mark in between to get the sheet reference like below.
= CONCATENATE (A2,”!”, B2)
Must not forget to enclose the exclamation mark in double quotation marks 🗯
- Wrap the above function in the INDIRECT function.
= INDIRECT (CONCATENATE (A2,”!”, B2))
- Hit Enter to reach the same results again.
Fun, isn’t it 🍭
Performing functions on Named Ranges
The INDIRECT function can not only recognize cell references and ranges but named ranges too. See for yourself.
Here is a list of some expenses (yeah, many of them) 💰
Turn them into a named range by following these steps:
- Select the list of expenses (the numbers only).
- Go to the Formulas tab > Name Manager.
- From the Name Manager dialog box, select New.
- Set a name for the defined range (we are setting it to Expenses).
- Click Okay.
The range is saved by the name Expenses 🎯
- Close the Name Manager.
- Activate any cell (Cell D2) and write the name of the range (Expenses) in it. Make sure not to make any spelling mistakes.
- In another cell write the INDIRECT function as follows:
= INDIRECT (D2)
- Hit Enter to see that the INDIRECT function will return the whole range that you saved by the name Expenses 💁♀️
Cool! But we will take it a step ahead.
- Now wrap the INDIRECT function in the SUM function as below:
= SUM (INDIRECT (D2))
Viola! We have the sum of the range Expenses. That’s how you can use the INDIRECT function to create an indirect reference to ranges 💪
You may try any function with that. For example, wrap the INDIRECT function above in the AVERAGE function to get the average of the said range.
= AVERAGE (INDIRECT (D2))