INDIRECT Worksheet Function

Home page:
[View without Frames]


=INDIRECT("'C:\temp\[Output" & TEXT(A1,"yyyymmdd") & ".csv]Output" & TEXT(A1,"yyyymmdd") & "'!a3")


The sheetname for the table may change, be deleted/recreated

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.



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.


  • 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   Document made with Nvu


1Company name  A1: Company name
2G1  A2: =INDIRECT("sheet" & ROW()-1 & "!G1")
3G1-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).

 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

 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.


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