VLOOKUP syntax

=IF($A2="","",IF(O$1="","",VLOOKUP($A2,HQA01!$A$24:$AG$525,6,FALSE)))

The above statement works great, but when I try to replace the HQA01 (worksheet name) with a cell reference it doesn't work anymore. I want to point to the cell that has that name of the sheet in it rather than hardcode each sheet name.

=IF($A2="","",IF(O$1="","",VLOOKUP($A2,$Q$1&"!$A$24:$AG$525",6,FALSE)))

When I try the &, or the concatenate function it appears to put quotes around the entire result and doesn't work. It's driving me crazy. PLEASE help, I think it's just syntax...

Thanks in advance...

=IF($A2="","",IF(P$1="","",VLOOKUP($A2,INDIRECT(HQA01!$A$24:$AG$525),6,FALSE)))

I tried a variety of ways, but I get a #VALUE error even when I do an INDIRECT on the full name without trying to use a cell reference.

=IF($A2="","",IF(O$1="","",VLOOKUP($A2,HQA01!$A$24:$AG$525,6,FALSE)))

The above statement works great, but when I try to replace the HQA01 (worksheet name) with a cell reference it doesn't work anymore. I want to point to the cell that has that name of the sheet in it rather than hardcode each sheet name.

=IF($A2="","",IF(O$1="","",VLOOKUP($A2,$Q$1&"!$A$24:$AG$525",6,FALSE)))

When I try the &, or the concatenate function it appears to put quotes around the entire result and doesn't work. It's driving me crazy. PLEASE help, I think it's just syntax...

Thanks in advance...

=IF($A2="","",IF(P$1="","",VLOOKUP($A2,INDIRECT($P$1&"!"&"$A$24"&":"&"$AG$525"),6,FALSE)))

Resolved by a suggestion on this LINK ---
http://en.allexperts.com/q/Excel-105...able_array.htm

=IF($A2="","",IF(P$1="","",VLOOKUP($A2,INDIRECT($P$1&"!"&"$A$24"&":"&"$AG$525"),6,FALSE)))

Resolved by a suggestion on this LINK ---
http://en.allexperts.com/q/Excel-105...able_array.htm

Threads merged. Salfonsi, please don't do that.