how do i find data in several worksheets at one time

[kimmar62]

I have one workbook for sterling silver jewellery. Within the workbook I have
several suppliers who each have their own worksheet with their products
listed and prices. I need to compare the prices from each supplier for one
item.
An example: Each supplier has an item called "Frog Earrings" and without
going through each worksheet how to I get the price from each supplier for
the "Frog Earrings", for comparison? Is there a way to do this?

Thanks

 

[GSnyder]

I would probably lay it out with the jewelry description in column A and then
put several VLOOKUP functions in columns B,C,D, etc. depending how how many
suppliers you have.

Assuming you have Sheets called "Supplier 1", "Supplier 2", etc. with
descriptions in column A and prices in column B, the formula in B2 in your
"master" sheet for comparison would be: =VLOOKUP(A2,'Supplier
1'!$A$1:$B$500,2,0)

This will look for the value in A2 ("Frog Earrings") in the A column of the
Supplier 1 sheet and will return the price from the 2nd column (column B).

Similarly, you'll put a formula in column C of your master sheet for
supplier 3, etc.

If they don't have that item (or if it's spelled differently) you'll get an
#N/A error. It's probably better to use an item code or something like that.

I hope this helps!

 

[Jim Cone]

My commercial Excel add-in "XL Companion" will do that.
Its Find and List feature creates a new worksheet listing all found items.
The list includes the sheet name and row number where found and
each item in the list is hyperlinked to the original data.

Email me direct and ask for the free trial version.
Remove xxx from my email address... james.coneXXX at comcast.netXXX
Please provide your real name and geographic location.
--
Jim Cone
Portland, Oregon USA



"kimmar62"
<[email protected]>
wrote in message
I have one workbook for sterling silver jewellery. Within the workbook I have
several suppliers who each have their own worksheet with their products
listed and prices. I need to compare the prices from each supplier for one
item.
An example: Each supplier has an item called "Frog Earrings" and without
going through each worksheet how to I get the price from each supplier for
the "Frog Earrings", for comparison? Is there a way to do this?

Thanks