INDIRECT Worksheet Function
The INDIRECT worksheet function has two arguments; ref_text and a1. Ref_text is some representation of a cell address. It can be a string like “B10″, a range reference like C10 where cell C10 contains the string “B10″ or even the result of a formula like “B” & (5*2).
The a1 argument can be True or False. True indicates that the ref_text you supply is in the A1 style. True is the default of this optional argument and can be omitted. False indicates that ref_text is in the R1C1 style.
Obviously passing a string like “B10″ to INDIRECT isn’t very efficient. You can just refer to it directly like =B10. However, if you have to build your cell address from other cells, INDIRECT can be very useful. Here’s some examples of INDIRECT in action.
You can also use INDIRECT to reference cells on another sheet or in another workbook. It won’t work on closed workbooks, so beware of that. The syntax can be tricky, so I usually create a real reference without indirect to see what it should look like, then build my ref_text using the example.
In the examples above, I used a sheet name and workbook name that had a space in it. This is to illustrate that you need apostrophes around the worksheet and workbook names in some circumstances. It’s a good practice to include them even if you don’t need them. The whole point of INDIRECT is to build ref_text from cells that may change. If you start with a ref_text that doesn’t need apostrophes and a cell changes such that you do need apostrophes, you’ll get a #REF! error. Better safe than sorry.
Hi,
I am using the following inputs for a combined VLOOKUP and INDIRECT function. I think I have put everything necessary but I still get the #REF! error.
VLOOKUP(A3,INDIRECT(”‘”&”I:\Finance\2004\IT DEPARTMENT\IT PROJECTS TRACKING\1 - Individual files\”&B78&”.”&B79&”.”&B80&”\["&C1&" "&B78&"."&B79&"."&B80&".xls]Template”&”‘”&”!$A$1:$ZZ$200″,TRUE),5,FALSE)
It will be great if you could tell me where I may be going wrong so that I can use this combination to make my spreadsheet easier to use and modify.
Sincerely,
Neil
INDIRECT functin can be used on many occasions. for e.g. you have a range name from A1 to A200 even using dynamic reference like offset. But when you delete one of the rows there is problem.
you can write a detailed article giving all the possible examples of using INDIRECT and publish in this blog.
Hey Neil
Try Nesting the indirect statement in another indirect
i.e.
indirect(indirect(”Reference”))
This seems to get rid of the error. Don’t know why.
Dick-
Thank you so much for this help! First time on this site and WILL NOT BE MY LAST. I have been wanting to do this for years. I knew that it should work, but didn’t know the steps. Now I just have to remember where I wanted to use this…
This is one more Excel mystery off my list. Thanks again, and keep the knowledge coming.
Thanks,
Andy
is there a way to replicate the indirect function in vba code?
anki -
In VBA, it’s all working with strings, the way INDIRECT does. Just manipulate the strings in the expression:
Workbooks(String1).Worksheets(String2).Range(String3)
is like INDIRECT(”[Book1.xls]“&”Sheet1!”&”$A$1″)
I’m pulling (what’s left of) my hair out. I have an indirect function as follows:
=INDIRECT(”‘u:\production statistics\[Shell Prod Adj Factor Calcs.xls]Factor Calculations’!” & ADDRESS(138,2))
If the other workbook is open, the reference is successful and I get the resulting value. If the other workbook is not open, I get the #REF! error result. I’ve tried the nested indirect functions, but that doesn’t seem to solve my issue. Any guesses?
Tim - Indirect() just doesn’t work with a closed workbook.
There are some alternatives here: http://www.dailydoseofexcel.com/archives/2004/12/01/indirect-and-closed-workbooks/
Indirect is a great function. I have used it myself many times. The one thing that is not mentioned here is that it is a Volatile function. It will be recalculated every time a calculation executes (similar to the Now() function). So if you want to use a handful of these functions then more power to you, but if you have hundreds or thousands of these functions embeded in formulas that are slow to calculate (like VLookup or such) then performance may become an issue for you.
Here is another trick to avoid popups and #REF with INDIRECT if the other workbook is closed.
It seems that ISNUMBER is wrapping the error INDIRECT produces and so you can make an IF work on this.
=IF(ISNUMBER(INDIRECT(”‘[otherworkbook.xls]sheet1′!$A$1″));INDIRECT(”‘[otherwoorkbook.xls]sheet1′!$B$”&C2);”please open otherworkbook.xls to make it work”)
Of course it won’t update until you open the needed workbook.
Hi, i have a workbook with an expanding number of worksheets. i have a macro to copy a template sheet to the book and to re-name the sheet from Templ(2) to the project number (say) 2510. To set this new sheet up i need to navigate (under macro control) between the sheets in the book. My base macro needing to cater for all new sheets assigns the Project ID 2510 to a variable “tabName”. Some of the sheets are static like “summary” and can be reference by - Sheets(”summary”).Select however when in the same macro i try to get back to my new sheet with a similar Sheets(”tabName”).Select i get an out or range error returned.
i believe excel is not recognising the assignment as it will switch with (say) a Sheets(”Sheet9″).Select, but not using the variable name. investigated perhaps the use of INDIRECT but can’t find the syntax. Help please.
best regards,
Bob mcglynn
Bob: Remove the quotes around your variable name
INDIRECT is only necessary when the worksheet portion of the constructed reference could vary. Otherwise, for variable references into a static worksheet, INDIRECT(”R”&i&”C”&j,0) is always equivalent to INDEX($1:$65536,i,j) and INDIRECT(”‘whatever’!R”&i&”C”&j,0) is always equivalent to INDEX(’whatever’!$1:$65536,i,j).
INDIRECT can be useful when dereferencing names defined as static (NOT dynamic) ranges, e.g., YieldCurve_current referring to =XYZ!$A$1:$B$20 and YieldCurve_prior referring to =XYZ!$D$1:$E$20, and cell X99 contains a drop-down list containing Current and Prior. That cell’s value could be used in formulas like
=VLOOKUP(MonthEntry,INDIRECT(”YieldCurve_”&X99),2)
But the equivalent functionality could be provided by nonvolatile functions using a few more defined names, e.g., with some range named YieldCurveList containing Current and Prior and the drop-down list for cell X99 tied to that range, and the name YieldCurve_selected defined as
=CHOOSE(MATCH($X$99,YieldCurveList),YieldCurve_current,YieldCurve_prior)
the VLOOKUP formula could be rewritten as
=VLOOKUP(MonthEntry,YieldCurve_selected,2)
The same sort of approach could be used to refer to ranges in different worksheets. As long as there are only a few such ranges (CHOOSE itself is limited to 29, but length of name definitions would likely kick in before that), CHOOSE(MATCH(.),…) can replace the volatile INDIRECT.
Dick Kusleika:- many thanks for the response, have tried just about every combination with without etc etc. have just tried without to double check and i get a runtime error “9″ - “subscript out of range”
which i believe is an array error - but i cant see how to correct?
any suggestions greatfully recieved
regards
B
Bob: Subscript out of range in this case means there is no sheet with that name. I believe your problem is that you’re trying to find a sheet with the number 2510 and you need to be looking for the string 2510. This works
Dim tabName As String
tabName = "2510"
ThisWorkbook.Sheets(tabName).Select
End Sub
If tabName is a Long rather than a String, it will look for the sheet whose Index is 2510. If it’s a String, it will look for the sheet whose Name is “2510″. If 2510 is a number in your code, you can do:
Just make sure you’re passing a string to the Sheets property.
Dick:
Once again many thanks for the response and you are correct that works fine, and u have nailed my error correctly. i have tried a number of things around this and not found the solution. you provide that - Thanks.
i do however have a further problem, my macro is universal for all new sheets added, so i don’t know what the sheet id will be ahead of the game. i am using a variable “projNumber” picked off a cell within the new sheet (in this case 2510) to change the tab name. this works fine. i did think of your above point and have formatted all instances of the Proj Number as “Text”, hoping that when i picked the “number” up it would pick up with it a consistant Text Format as well.
However if i change your snippit to use my variable rather that the literal 2510, it fails with the same subscript error when i try to navigate back to the sheet from elsewhere in the workbook.
i believe you have identified the solution to my error, but my limited vba does not permit me to find the correct syntax when using Variable assignments.
Regards
Bob
Is there a way to structure an INDIRECT function that references a range of worksheets? E.g., I have a Workbook that has a summary sheet of the worksheets contained. The trick here is that I will ADD to the number of worksheets and I would like to have the summary sheet include the new worksheets.
For example I have a workbook with the following worksheets: Summary, Data1, Data2
In cell A1 of each of the “Data#” sheets is a number that will be summed in the summary sheet.
In the summary sheet I’ve contructed the following SUM function =SUM(”Data1:Data2!A1″) with successful results. However if I add a new Data worksheet, I must modify the function to include new sheet. One way to address this is to insert the new worksheet between the Data1 and Data2 worksheets. I will still have successful results in this case.
However, I would like the add the new worksheets after the current last worksheet, and just maintain a cell with the number of the last worksheet.
For example; before adding a new sheet to the workbook, cell A1 in the summary sheet would contain the value 2, to represent the current last worksheet “Data2″
I’ve constructed the following function =SUM(INDIRECT(”Data1:Data” & A1 &”!A1)) This results in a #REF error for which I cannot solve.
Is it possible to do what I wish to do, or is there an inherent error in the INDIRECT function handling of ranges of sheets?
Larry - INDIRECT can only return range objects, and 3D references aren’t range objects, so no way to generate dynamic 3D references in Excel.
You could use the classic approach of adding blank worksheets as bounds for multiple worksheet blocks. For example, insert a worksheet named Data_Begin just before Data1 and another worksheet named Data_End just after Data2. Change your formula to
=SUM(Data_Begin:Data_End!A1)
or with cell A1 the active cell in ANY worksheet define the name Data referring to
=Data_Begin:Data_End!A1
note that the cell address is RELATIVE and change the cell formula to
=SUM(Data)
You could hide Data_Begin and Data_End, and as long as the active worksheet is between Data_Begin and Data_End when you insert new worksheets, those new worksheets will also be between Data_Begin and Data_End.
fzz:
I have a workbook with a lot of INDIRECT functions in it, and I’m specifically looking for advice on how to replace them with CHOOSE() functions to speed performance.
Here’s a sample of one of the formulas:
=INDEX(INDIRECT(ShipChooser1Up),1,7)
Where ShipChooser1Up grabs the name of a named range for the INDEX function to pull a specific reference from.
I’m trying to get the same functionality with this code snippet:
=INDEX(CHOOSE($DJ$5,$DJ$8,$DJ$9),1,7)
Where DJ8,DJ9,etc, just have the names of the named ranges I want to switch between.
However, when I do this, I get #REF errors.
What am I missing here? (If I can figure out how to do this, I can make about 9,000 INDIRECT() calls go away.)
Ken:
If DJ8 and DJ9 “contain” named ranges, you would need to use INDIRECT() on them. How else would EXCEL know to use them (indirectly) as range names instead of (directly) as the “ranges” they are? Since the “ranges” are only a single cell, your “7″ parameter causes the #REF! error — it’s trying to refer to a cell outside of a single-cell range.
Try one of these instead:
=INDEX(INDIRECT(CHOOSE($DJ$5,$DJ$8,$DJ$9)),1,7)
=INDEX(CHOOSE($DJ$5,Range1,Range2),1,7)
Thank you - I’ll try using the second formulation.
My problem is that the pages I’m working with use INDEX(INDIRECT()) to pull data from different workbook tabs; it’s basically a report generator. All well and good, until it’s doing calls on over 4000 cells with INDIRECT statements, and some of them with error catching IF statements that require an evaluation of the INDIRECT content before deciding if it’s going to print blank or a value.
Second formulation works.
Hi,
I have a summary sheet followed by a number of data sheets as well. Each of the data pages has a cost code column that a number is input into.
The summary page has room for a maximum of 30 data sheets, but we have never gotten close to it.
There is a range N10:N39 on the summary page which contains the data sheet tab names.
I use a counta(N10:N39) TO give me the number of actual datasheets in the workbook.
On the breakdown sheet I have the formula F1 below, which sums all the values on all the data sheets if they correspond to the number assigned to the breakdown item. I am trying to neplace the 16 in the $N$16 references with a value generated by a counta function (which will give me the amount of data sheets) “(COUNTA(Sum!$B$10:$B$39)+9)” — there are 9 header rows on the summary page, plus 7 referenced data sheets = 16!
=SUMPRODUCT(SUMIF(INDIRECT(”‘”&Sum!$N$10:$N$16&”‘!$A$10:$A$44″),TBD!A2,INDIRECT(”‘”&Sum!$N$10:$N$16&”‘!$O$10:$O$44″)))
I would have thought just replacing the $N$16 with INDIRECT(“$N$”&(COUNTA(Sum!$B$10:$B$30)+9)
which gives
=SUMPRODUCT(SUMIF(INDIRECT(”‘”&Sum!$N$10:INDIRECT(“$N$”&(COUNTA(Sum!$B$10:$B$30)+9)&”‘!$A$10:$A$44″),TBD!A2,INDIRECT(”‘”&Sum!$N$10:INDIRECT(“$N$”&(COUNTA(Sum!$B$10:$B$30)+9)&”‘!$O$10:$O$44″)))
would work, but I get “the formula contains an error” and it highlights the first occurance of “$N$”
I am struggling to see the problem. I would love if someone could point it out. It’s more than likely syntax.
If you can come up with a better (smaller) function, by all means post and I will try it out!
Cheers!
I am trying to use an INDIRECT function to define a data series in an XY plot, but the series formula won’t accept the INDIRECT function (I assume it has something to do with it being a volitile function). I have tried using the indirect function to name a range and enter the range as the series, but it still won’t accept even the named range. The named range definitely selects the correct data, but I guess the series formula still sees it as a volitile entry.
In my spreadsheet I need to allow the user to manually enter a start and end line for the plot, and it needs to be flexible so that the start and end line can be changed to obtain a better curve fit for the data. Typical strategies for defining dynamic ranges don’t work here because I need to be able to define the end of the data series manually.
Any help would be appreciated.
PJ -
Charts often crap out when INDIRECT is used in a name definition.
If you can use OFFSET, instead of
=OFFSET($A$1,1,0,COUNT($A:$A)-1,1)
have the user put the start row in D1 and finish row in D2 and use this:
=Offset($A$1,$D$1,0,$D$2+1-$D$1,1)