Indirect Excel formula returning REF

agwalsh used Ask the Experts™
hi Folks
I'm attaching a file with an indirect function. I want it to reference the sheet name in the dropdown (D1) and return a value from B4 in that that sheet.
This is my formula..=INDIRECT(" ' "& $D$1 &" ' !"& B4)
What am I missing? Thanks
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

pls try


Open in new window

Hi - you got it close but a few extra spaces and a misplaced " :-

=INDIRECT( "'"&$D$1&"'!B4" )

edit: post crossed :)


Brilliant! That worked beautifully. Grrrr about the missing quotation marks. I knew it was something small. Just one other question.. I got this working with a Vlookup referencing a file in the same folder where I had the file name in a cell in the same folder
=VLOOKUP(A4,INDIRECT("'[" & $F$1 &"]"&$D$1&"'!"&"$A$3:$B$13"),2,FALSE)
Just out of curiosity - how would I use this formula to reference a file in another folder....Thanks. :-)
The easiest way to try it is to create a vlookup function using a direct link, - ie: open both, create the vlookup in one and then where needed switch to the other so Vlookup will contain a link to the other file. once you have that working close both files and open the one with the vlookup - you should find that vlookup now contains the URL of the target file. eg:
=vlookup(a1,'[Tracker 2016 v4.xlsx]Overall'!C1:C100

The full filename is wrapped in [] with the usual single quote including the sheetname, then the ! cell address.

From this you can work out which bits need to be indirected for your operational version


Excellent help. Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial