MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Any lookup using multiple worksheets


Posted by Kurt on February 07, 2001 10:24 AM

Hi,

A friend is trying to test my Excel limits and I think he's found it.

He wanted to know if there is anyway to do a lookup or otherwise change the worksheet reference in a formula so that the result of the formula changes when the data needed in on a different worksheet.

=VLOOKUP(H9,Kurt!$E$7:$J$10,5,FALSE)

In other words, is there any way to have the sheet reference " Kurt!$E$7:$J$10 " replaced with a cell reference that changes to another sheet?

Thanks.
Kurt


Posted by Mark W. on February 07, 2001 2:31 PM

=VLOOKUP(H9,INDIRECT("Sheet"&A1&"!$E$7:$J$10"),5,FALSE)

According to this formulation a sheet number in cell A1
will determine which worksheet is referenced (i.e, Sheet1,
Sheet2, etc.) by the VLOOKUP() function.

Posted by Kurt on February 08, 2001 6:44 AM

Mark,

Thank you very much for your help. Your answer was on the money.

Kurt