Using Sheetname in Vlookup as the sheetname for the array to lookup

I have 2 workbooks: WorkbookA, WorkbookB

Each Workbook has the same Sheetnames: Jan, Feb, Mar, etc...

In workbookA, I want to use the following formula:

VLOOKUP(Variable,[WorkbookB.xls]Jan!Truck_Schedule,MATCH(Date,[WorkbookB.xls]Jan!$N$5:$AS$5),0)

Where it says "Jan" I would like to use the sheetname of the sheet where this formula is located.

Something like: VLOOKUP(Variable,[WorkbookB.xls]SheetName!Truck_Schedule,MATCH(Date,[WorkbookB.xls]SheetName!$N$5:$AS$5),0)

You would use INDIRECT for this, which enables you to convert a string that represents a range reference into the actual reference, but this will only work if both workbooks are open in the same instance of Excel. Do you have a cell that contains SheetName?

Pete

I do have a cell that contains the sheetname. However, I would need to be able to do this without WorksheetB being open. Any way to complete?

That is the limitation with INDIRECT.

If you are prepared to download a free add-in (morefunc - do a Google search for site where you can download it from), then you could use the function INDIRECT.EXT, but for that to work you would need to supply the full path as well as the filename and sheet name.

Hope this helps.

Pete

It is a corporate environment that will not allow add-ins. Any other solution?