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
Sheets(tabName).SelectINDIRECT 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:
tabName = CStr(2510)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