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.

The INDIRECT Function Explained. The INDIRECT function is part of the Lookup and Reference group of functions in Excel.

Video Tutorial

Method. Let us look at a basic example of INDIRECT. This formula will pull the value ‘600’ in cell G2 into cell A2.

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.

However,the AVERAGE function below returns an error. Explanation: =AVERAGE (“Scores”) returns an error because Excel cannot calculate the average of a text string!

The INDIRECT function below does the trick.

How does indirect work Excel?

ADDRESS returns a string with a cell’s address.

INDIRECT converts a string to a cell or range.

OFFSET returns a cell or range offset from another.

INDEX returns cells inside a specified range.

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.