VLOOKUP Function's Table_Array Argument

[sohrabc]

I am using the VLOOKUP function in a worksheet named REC-ISS in a workbook named EE-Cardex.xls.
However, the Table Array to be referred is B22:P33 in different worksheets, in another workbook named MPR-Jun10.xls.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The name of the particular worksheet required to be used, is specified in cells H3, N3, T3, Z3, etc. of the worksheet REC-ISS of the workbook EE-Cardex.
<o></o>
Hence, if the contents of the cells H3, N3, T3, Z3, etc. are ABC, XYZ, MNO, UVW respectively (being the names of the worksheets in the workbook MPR-Jun10), then the Table-Array argument of the VLOOKUP function should accordingly change in columns H, N, T, Z, (as per the content of H3, N3, T3, Z3, etc.), as under:
'[MPR-Jun10.xls]ABC'!B22:P33
'[MPR-Jun10.xls]XYZ'!B22:P33
'[MPR-Jun10.xls]MNO'!B22:P33
'[MPR-Jun10.xls]UVW'!B22:P33
<o></o>
I need to know how the Table_Array argument needs to be described to accomplish this, i.e. what should be in place of ??? in the Table_Array argument '[MPR-Jun10.xls]???'!B22:P33, or how best this could be simply achieved.
<o></o>
Many thanks for your kind help.

 

[Aladin Akyurek]

Have a look at INDIRECT if MPR-Jun10.xls can be kept open, otherwise INDIRECT.EXT with examples at this board.

 

[sohrabc]

Thank you Alladin for your kind tip.

Since MPR-Jun10.xls can be kept open, I tried using INDIRECT but had to struggle a lot to get the right syntax.

However, I'm very happy to say that I was able to get the result I wanted, by using the following for the Table_Array argument in the 'H' column of my spreadsheet, where the name of the specific worksheet of the MPR-Jun10.xls workbook (which was required to be used to refer to the Table_Array B21:P33) was specified in the cell H3 of the worksheet REC-ISS of the workbook EE-Cardex.xls :
INDIRECT("'[MPR-Jun10.xls]"&$H$3&"'!$B$21:$P$33")

Thanks again.

Regards