Indirect Excel formula returning REF

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
LVL 1
agwalshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

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

Open in new window

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

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

edit: post crossed :)
agwalshAuthor 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. :-)
regmigrantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
agwalshAuthor Commented:
Excellent help. Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.