INDIRECT Function
The indirect function in excel returns a reference to a range. We can use this function to create a reference that won't change if row or columns are inserted in the worksheet. Or, cause it to create a reference from letters and numbers in other cells.The INDIRECT function has two arguments:
- ref_text: A cell reference or text string
(or both), that create the range reference. The referenced range can be a
cell, a range of cells, or a named range.
- a1: TRUE or FALSE. Does the reference use A1 reference style? If this argument is TRUE, or omitted, the ref_text is A1 style. If the argument is FALSE, the ref_text is R1C1 style.
 |
| Indirect Function Excel |
:If the INDIRECT formula refers to a
different workbook, that workbook must be open, or the formula will return a
#REF! error.
Lock a Cell Reference
If we
create a simple link to a cell, e.g. =A3, then insert a row above
row 3, our formula will automatically change to =A4. To prevent
this change, we can use the INDIRECT function.
To
demonstrate the difference, we'll create two formulas -- one that uses a simple
link, and one that uses a text string with an INDIRECT function.
- In
cell A3, type a number (505 in this example).
- In
cell B2, type the following formula:
=INDIRECT("A3") - Press
the Enter key, and the formula will return the number in cell A3.
- In
cell C2, type the following formula:
=A3 - Press
the Enter key, and the formula will return the number in cell A3.
- To
insert a row, right-click on the Row 3 button, and from the pop-up menu,
choose Insert.
- As
shown below, cell B2, which uses the INDIRECT function, now returns 0,
because cell A4 is empty. The text string, "A3", in
the INDIRECT formula did not change when the row was inserted.
Create a Reference from a Cell Value and Text
Create a Reference to a Different Sheet
Refer to a Named Range
T
You can read more about indirect from the below article:
The below video shows you about INDIRECT function of excel:
No comments:
Post a Comment