How do you do an indirect cell reference in Excel?
Using Excel INDIRECT function to lock a cell reference
- Enter any value in any cell, say, number 20 in cell A1.
- Refer to A1 from two other cells in different ways: =A1 and =INDIRECT(“A1”)
- Insert a new row above row 1.
How do you reference an indirect range?
Refer to a Named Range
- In cells A1:B5, type headings and numbers, as shown at right.
- Name cells A2:A5 as East, and cells B2:B5 as West.
- In cell D2, type the name of one of the ranges, e.g. East.
- In cell E2, type the formula: =SUM(INDIRECT(D2))
How do you in text reference a cell in a formula?
Usually you type =A1 for referring to the cell A1 in Excel. But instead, there is also another method: You could use the INDIRECT formula. The formula returns the reference given in a text. So instead of directly linking to =A1, you could say =INDIRECT(“A1”).
How do you use indirect in Excel?
Objective. Use the INDIRECT function to indirectly reference cells and ranges.
What does the indirect function do in Excel?
For example,the AVERAGE function below uses the named range Scores. Explanation: the named range Scores refers to the range D1:D3.
How does indirect work Excel?
ADDRESS returns a string with a cell’s address.
How to use the indirect formula Excel?
The INDIRECT formula can be applied with the name range also. But, first, give a name to a range of numbers. Input a string “sales “ in the cell G45. Now find the AVERAGE of sales using the INDIRECT formula. INDIRECT refers to G45, which has reference string sales, and sales is the reference of cells E45 to E48.