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.
Neil Sinha:
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,
21 April 2005, 10:51 amNeil
r.venkataraman:
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.
29 August 2005, 10:32 pmMarc Davis-Marsh:
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.
7 September 2005, 5:02 pmAndy:
Dick-
21 September 2005, 12:57 pmThank 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
anki:
is there a way to replicate the indirect function in vba code?
4 January 2006, 4:36 pmJon Peltier:
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″)
5 January 2006, 7:33 amTim:
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?
19 November 2007, 2:44 pmDoug Jenkins:
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/
19 November 2007, 9:05 pmJim Thomlinson:
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.
22 November 2007, 2:28 pmGeorges:
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.
26 February 2008, 7:16 am