# INDIRECT Worksheet Function

Location: http://www.mvps.org/dmcritchie/excel/indirect.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.