Indirect Excel formula returning REF

agwalsh
agwalsh used Ask the Experts™
on
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
EE_indirect_dynamic_formula.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Rgonzo1971
Top Expert 2016
Commented:
Hi,

pls try

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

Open in new window

Regards
regmigrant

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

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

edit: post crossed :)
agwalsh

Author

Commented:
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. :-)
Commented:
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
agwalsh

Author

Commented:
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