MrExcel Publishing
Your One Stop for Excel Tips & Solutions

vlookup


Posted by Frustrated on August 22, 2001 11:36 AM

I have nine worksheets in a workbook as data sheets for specific manufacturers. Each one lists a product number in column A and price in column J. Each of these sheets is updated at different time throughout the day and week. As a part of a summary work sheet I want the user to be able to enter the manufacturer in A2 and product number in B2. I would like C2 to return the most recent price from the nine other sheets. Is there a way to look this up without combining the 9 data sheets on a daily (or more often) basis and keying a vlookup off that?


Posted by IML on August 22, 2001 11:44 AM

If I understand correctly try this. First, name each of your tabs the same name as the manufacturer.
Second, use data validation's list feature to allow entry of only the manufacter's name as they appear on your tabs.
Last, your formula in C1 on the summary sheet should be:

=VLOOKUP(B2,INDIRECT(A2&"!A:J"),10,FALSE)

This way you get an updated manufactuer sheet, you can simply delete the old, add the new one, and rename it.

Good luck