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
