How do I search entire workbook and return a value on another worksheet

Hi All
I am working on a price list with the following:
•Products are grouped per product category per worksheet and each containing about no less than 2000 lines per worksheet. I have total of 9 worksheets. (Each worksheets has formulas like currency conversion, markup prices etc.. to arrive to a retail price)

Question:
I need to create a summery sheet for most popular products.
How do I get excel to search the entire workbook and return the value on a designated cell, so that I have the price a specific product in the summary sheet and me not to do search every time and I can also print a simple pricelist say for a smaller customer.
Simple English is very much appreciated as I am not a very technical person.
Thanking you in advance.

Hi,

Welcome to the Forum.

Please go through the following URLs for details on search multiple sheets in a workbook and return a value.

http://www.excelforum.com/excel-form...-workbook.html

OR

http://en.kioskea.net/faq/6215-looku...ultiple-sheets

Thank for the help.

Having difficulty translating it to my sheet.
I have attached a sample sheet. Can anyone perhaps do it in my attached spreadsheet so i can understand better.

Summary page is where i want the results.

Thanks.

Hi,

I have updated the formula in the attached file. However, there are some products which are reflecting in 2 tabs with different prices & in this case the formula would pick up the matching price from the first sheet.

Regards,
Chandra


Please click on "* Add Reputation" if this was helpful.