Link to home
Start Free TrialLog in
Avatar of agwalsh
agwalsh

asked on

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
SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi - you got it close but a few extra spaces and a misplaced " :-

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

edit: post crossed :)
Avatar of agwalsh
agwalsh

ASKER

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. :-)
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of agwalsh

ASKER

Excellent help. Thank you.