Indirect function within Vlookup

**garethjohn** · 05-25-2011, 08:05 AM

Hello one and all!

I have the following issue:
I have 2 spreadsheets. 1 Master and 1 Slave.
The slave has a number of worksheets containing sets of data.

The Master collects all this data.

I am using the following formula.

=IF(ISBLANK(VLOOKUP($J97,[Raw.xls]ELTO!$A$2:$R$99,12,FALSE)),"",VLOOKUP($J97,[Raw.xls]ELTO!$A$2:$R$99,12,FALSE))

Where RAW.xls is the slave file holding all the data. (ELTO is the worksheet name)
J97 is the reference cell , and holds data such as Project A, B, C

Now if within the Master file, column I had the word ELTO or something else, hwo do i incorporate this within the formulsa?
The next worksheet I want to use is called Morizz, and so on.

I know I have to sue INDIRECT but unsure where.

Help!

Thanks!

**Bob Phillips** · 05-25-2011, 08:11 AM

Try

=IF(ISBLANK(VLOOKUP($J97,INDIRECT("[Raw.xls]"&$I97&"!$A$2:$R$99"),12,FALSE)),"",
VLOOKUP($J97,INDIRECT("[Raw.xls]"&$I97&"!$A$2:$R$99"),12,FALSE))

**garethjohn** · 05-25-2011, 08:29 AM

Hi Bob
Thanks

its not working. I beleive there is an issue with the second RAW.xls

I am using the following:

=IF(ISBLANK(VLOOKUP($J115,indirect("[Raw.xls]"&$I115&"!$A$2:$R$99),15,FALSE)),"",VLOOKUP($J115,indirect("[Raw.xls]"&$I115&"!$A$2:$R$99),15,FALSE))

**NBVC** · 05-25-2011, 08:32 AM

In case some of the sheetnames include spaces, it is always safer to include single quotes...

eg.

=IF(ISBLANK(VLOOKUP($J97,INDIRECT("'[Raw.xls]"&$I97&"'!$A$2:$R$99"),12,FALSE)),"",VLOOKUP($J97,INDIRECT("'[Raw.xls]"&$I97&"'!$A$2:$R$99"),12,FALSE))

Also, note that INDIRECT does not work with closed source workbooks.

You will need INDIRECT.EXT after installing addin from here: Morefunc.