INDIRECT Worksheet Function
Location: http://www.mvps.org/dmcritchie/excel/indirect.htm
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]
Dates
=INDIRECT("'C:\temp\[Output" & TEXT(A1,"yyyymmdd") & ".csv]Output" & TEXT(A1,"yyyymmdd") & "'!a3")
VLOOKUP
The sheetname for the table may change, be deleted/recreated
=VLOOKUP(E5,INDIRECT(A1&"!$B:$AE"),30,FALSE)
INDIRECT Worksheet Function
|
Returns the
reference specified by a text string. References are immediately
evaluated to display their contents. Use INDIRECT when you want to
change the reference to a cell within a formula without changing the
formula itself.
Syntax
INDIRECT(ref_text,a1)
Ref_text
is a reference to a cell that contains an A1-style reference, an
R1C1-style reference, a name defined as a reference, or a reference to
a cell as a text string. If ref_text is not a valid cell reference,
INDIRECT returns the #REF! error value.
A1
is a logical value that specifies what type of reference is contained
in the cell ref_text.
- If a1 is
TRUE or omitted, ref_text is interpreted as an A1-style reference.
- If a1 is
FALSE, ref_text is interpreted as an R1C1-style reference.
Remarks
- If
ref_text refers to another workbook (an external reference), the other
workbook must be open. If the source workbook is not open, INDIRECT
returns the #REF! error value. [INDIRECT is not supported for closed workbooks.]
 The content in this box only was created by
pasting into Nvu's WYSIWYG HTML editor from Excel's HELP  |
| | A | B | C |
| 1 | Company name | |
A1: Company name |
| 2 | G1 | |
A2: =INDIRECT("sheet" & ROW()-1 & "!G1") |
| 3 | G1-1 | |
A3: =INDIRECT("sheet" & ROW()-1 & "!G1") |
Use of INDIRECT and HYPERLINK for a Summary Sheet
The
worksheet name is in Column A (which could be a company name), then
in column B place a formula to extract the value in Cell B2 of a company sheet,
this formula includes a hyperlink to take you to cell A1 of the company sheet,
to return to original summary sheet use Alt+ArrowLt (which you can
set up on a five button mouse).
| | A | B |
| 1 | Company |
Revenue |
| 2 | Sheet166 |
20,000 |
| 3 | Sheet167 |
15,000 |
| 4 | Sheet168 |
24,000 |
| 5 | Sheet169 |
20,000 |
| 6 | Sheet170 |
missing |
| 7 | Sheet171 |
missing |
| 8 | Sheet172 |
missing |
B2: =IF(TRIM(A2)="","",IF(ISERROR(INDIRECT("'" & A2 & "'!$A$1")),"missing", HYPERLINK("#" & "'" & A2 & "'!$A$1",INDIRECT("'" & A2 & "'!$B$2")) ))
SUM a Range that is specified as INDIRECT
| | A | B | C |
| 1 | b2 |
2 | |
| 2 | b3 |
4 | |
| 3 | b4 |
8 | |
| 4 | | | 'B2:B3 |
| 5 | 4 |
| A5: =INDIRECT(A1) |
| 6 | 12 |
| A6: =SUM(INDIRECT(A1&":"&A3)) |
| 7 | 14 |
| A6: =SUM(INDIRECT("B1:B3",True) |
| 8 | 14 |
| A6: =SUM(INDIRECT(C4)) |
In the example with C4 make sure
that here are no spaces before the range. A leading space
would result in a #REF! error.
Case-insensitive compare with INDIRECT (#exactcell)
A case-insensitive compare to always compare cell A5 to cell C5 regardless of repositioning, insertions, deletions.
=IF(EXACT(INDIRECT("A5"),INDIRECT("C5")),"ok","modify")
Related
You are one of many distinguished visitors who have visited my site here or in a previous location
since this page was created on September 24, 2001.
Visit [my Excel home page]
[Index page]
[Excel Onsite Search]
[top of this page]
Please send your comments concerning this web page to: David McRitchie
send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved