=INDIRECT(text_range_address) Worksheet Function
The INDIRECT function is useful if you want to get a value from a cell, but
the cell you want depends upon something else.
For instance, suppose you have a workbook with Sheets, named "JAN", "FEB",
"MAR", ... "DEC". On each sheet, Row 1 is a header, Row 2 is
for Day 1, Row 3 for Day 2, etc.
On an Schedule sheet, you need to get a value from Column "C", for March 10.
The address you would want is "=MAR!C11"
Now, suppose you on sheet "Sched" you have a cell at B2 that is for the month
"MAR",
Cell B3: is the Day number of the Month: 10.
Cell B4: is where you want the value returned.
If you put in B4: =MAR!C11, then whatever the values for B2 and B3, you will
get March 10.
- If you put in B4: =INDIRECT(B2 & "!C" & B3+1), excel will
- Read B2 to get "MAR"
Read B3 to get 10, add 1 to get the row number 11
Concatinate the three parts to get "MAR!C11"
INDIRECT will then fetch the value stored at the address "MAR!C11"
Caution: INDIRECT is a slow function.
It adds many intermediate steps to an Excel recalculation. If you
use hundreds or thousands of INDIRECT functions in a workbook, the recalculation
will slow to a crawl. OFFSET and INDEX are much faster ways to create
dynamic references.
---------------
See Also
Data Validation with INDIRECT(RangeNames)
http://www.contextures.com/xlDataVal02.html
There is some talk
about how INDIRECT cannot work with Dynamic Ranges Names
A Dynamic Range is one that has an indeterminate
number of rows or columns.
=Offset(AnchorRange, RowOffset, ColsOffset, Numrows, NumCols) is an
example
Another example is a range that may start at the Range Name: "List1Top"
continue to the bottom of the list.
You can define such a list as:
=Offset(List1Top, 0, 0, COUNTA(Offset(List1Top,0,0,10000,1)), 1)
- Explaination:
- List1Top is a named range (See Insert, Name, Define) whose address
might be Sheet2!$C$2
Offset(List1Top,0,0,10000,1) is a range that starts at List1Top and is
10000 rows long, 1 column wide.
COUNTA(Offset.....) will count how many cells have numbers or text in the
10000 cells.
Assuming there are no blank lines in the list, then CountA tells us how many
rows are in the list.
=OFFSET(List1Top,0,0,CountA(.....), 0) retrieves the entire list.
There is confusion whether INDIRECT can work with dynamic ranges.
It can. But the dynamic range must return, not a range of cells, but
the TEXT ADDRESS of those cells.
- Example:
- Range "Anchor" is $D$5.
- In cell $E$7, we have the TEXT "Sheet2!$F$15".
- In the target cell Sheet2!$F$15, we have the contents "Target Cell
Found"
- Somewhere on Sheet1, we enter the formula
- =INDIRECT(Offset(Anchor,2,1))
- the result will be "Target Cell Found"
- Offset() returns the contents of cell $E$7 (2 rows down, 1 row
right from Anchor)
- The contents of $E$7 is the address: Sheet2!$F$15.
- INDIRECT() returns the value from Sheet2$F$15.
Usually there is little need to use INDIRECT on an Offset.
Typically what you want is a block of cells directly supplied by the OFFSET()
function.
