• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Indirect Formula Help

B

BigBuilder

New Member
I'm trying, (unsuccessfully), to use the Indirect formula to dynamically change spreadsheets in my workbook.
My 'starter for 10' formula was:
VLOOKUP(INDIRECT("'",&C2&"'!"&$G$17&"'",&C2&"'!$G$14:$AO$607"),2,FALSE)
where C2 =
Patient Data - Latest
G17 = NUFFIELD HEALTH, THE GROSVENOR HOSPITAL, CHESTER in another worksheet, (Patient Data - Latest).
So, the whole thing should end up looking like:
VLOOKUP('Patient Data - Latest'!$G17,'Patient Data - Latest'!$G14:$AO$607,2,false)
which does work in it's 'raw' form and brings back 27.
I'm getting a #REF! when I try and replicate it using the INDIRECT and I've no idea why.
I've tried to 'fool' the INDIRECT by concatenating the spreadsheet reference bit of the VLOOKUP together in another cell then referencing it in the INDIRECT:
CONCATENATE("'",C2,"'!$G17",B13,"'",C2,"'!$G$14:$AO$607")
So, I'd end up with:
=VLOOKUP(INDIRECT(B12),2,FALSE) where B12 is where the concatenate is.
It didn't like that either.
I've looked on a few websites and I can't see anything wrong with what I've done.
I've also used INDIRECT before in another workbook - without a problem so I'm really puzzled.

Thanks for any responses. If more info's required, please let me know.
 
I think this is what you want
=VLOOKUP(INDIRECT("'"&C2&"'!$G$17"),INDIRECT("'"&C2&"'!$G$14:$AO$607"),2,FALSE)

You can't put the indirect across the first , in the Vlookup formula, each section requires its own Indirect as they are separate ranges
 
Hi ,

My suggestion would be :

=VLOOKUP(INDIRECT("'" & C2 & "'!" & "$G$17"), INDIRECT("'" & C2 & "'!" & "$G$14:$AO$607"),2,FALSE)

Narayan
 
Thanks to you both, your suggestions worked perfectly.
The crucial info I was missing was that the INDIRECT needs splitting in a VLOOKUP.
Just when you think you're pretty good at Excel, a problem like this comes along which reminds you that you aren't.#
(Maybe the above should be part of my signature for this site, assuming that someone else hasn't already used it !)
Thanks again.
 
You must log in or register to reply here.
Back
Top