How do i retreive data from another worksheet to a current one?

I am making a master pricing worksheet catalog. I am purchasing the same
products from mulitple vendors, all use the same product code as well. I have
set up 3 different vendors to compare prices on items. How do i put a formula
together on my master worksheet to scan these other 3 worksheets that are
identical to where i can have my master worksheet return the lowest price? So
if I type in product #1234 in the master sheet it will look at Sheet 1:3 and
return the lowest priced product?

DPH --

I think you'll want to use a lookup function -- either VLOOKUP or HLOOKUP
depending on how the other three tables are organized. The help
documentation is pretty good for these functions. To make it easiest:

1) Create a Named Range on each subsidiary worksheet with columns of part
numbers, prices, descriptions, anything else you'll want to retrieve. These
named ranges might be called Supplier1, Supplier2, etc.

2) If the subsidiary-page source info is organized in columns with part#,
Description, price (in A, B, C), then the VLOOKUP function looks like:

A B
1 Part # Desc
2 12345 =VLOOKUP(A2,Supplier1,2,false)

3) To return the lowest price, you'd do something like:

=min(VLOOKUP(A2,Supplier1,3,false),VLOOKUP(A2,Supplier2,3,false), etc.)

HTH

"dphi201" wrote:

> I am making a master pricing worksheet catalog. I am purchasing the same
> products from mulitple vendors, all use the same product code as well. I have
> set up 3 different vendors to compare prices on items. How do i put a formula
> together on my master worksheet to scan these other 3 worksheets that are
> identical to where i can have my master worksheet return the lowest price? So
> if I type in product #1234 in the master sheet it will look at Sheet 1:3 and
> return the lowest priced product?

Lets assume you type the product code in A1
And that the three sheets have product codes in A1:A1000 and prices in
B1:B1000
And these are called Sheet1, Sheet2, Sheet3
You want the price returned to B1
=MIN(VLOOKUP(A1,Sheet1!A1:B10-00,2,FALSE),VLOOKUP(A1,Sheet2!A1:B10-00,2,FALSE),VLOOKUP(A1,Sheet3!A1:B10-00,2,FALSE)

Now tell us if all three sheets have every code? If not then we must add
some error checking along the lines of this untested code
=MIN(IF(ISNA(VLOOKUP(A1,Sheet1!A1:B10-00,2,FALSE)),1E6,VLOOKUP(A1,Sheet1!A1:B10-00,2,FALSE)),IF(ISNA(VLOOKUP(A1,Sheet2!A1:B10-00,2,FALSE)),1E6,VLOOKUP(A1,Sheet2!A1:B10-00,2,FALSE)),IF(ISNA(VLOOKUP(A1,Sheet3!A1:B10-00,2,FALSE)),1E6,VLOOKUP(A1,Sheet3!A1:B10-00,2,FALSE)))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"dphi201" <[email protected]> wrote in message
news:[email protected]...
>I am making a master pricing worksheet catalog. I am purchasing the same
> products from mulitple vendors, all use the same product code as well. I
> have
> set up 3 different vendors to compare prices on items. How do i put a
> formula
> together on my master worksheet to scan these other 3 worksheets that are
> identical to where i can have my master worksheet return the lowest price?
> So
> if I type in product #1234 in the master sheet it will look at Sheet 1:3
> and
> return the lowest priced product?

Do you need the lowest price, or the identity of the lowest cost provider?

lowest price (assumes you name each vendors' price list range, and that each
range is 2 columns - item # & price):

=Min(lookup(item#, range1,2,0),lookup(item#, range2,2,0),lookup(item#,
range3,2,0))


"dphi201" wrote:

> I am making a master pricing worksheet catalog. I am purchasing the same
> products from mulitple vendors, all use the same product code as well. I have
> set up 3 different vendors to compare prices on items. How do i put a formula
> together on my master worksheet to scan these other 3 worksheets that are
> identical to where i can have my master worksheet return the lowest price? So
> if I type in product #1234 in the master sheet it will look at Sheet 1:3 and
> return the lowest priced product?

Lordy, that was a dumb omission: Not 'lookup', but VLOOKUP

=Min(Vlookup(item#, range1,2,0),Vlookup(item#, range2,2,0),Vlookup(item#,
range3,2,0))

"Duke Carey" wrote:

> Do you need the lowest price, or the identity of the lowest cost provider?
>
> lowest price (assumes you name each vendors' price list range, and that each
> range is 2 columns - item # & price):
>
> =Min(lookup(item#, range1,2,0),lookup(item#, range2,2,0),lookup(item#,
> range3,2,0))
>
>
> "dphi201" wrote:
>
> > I am making a master pricing worksheet catalog. I am purchasing the same
> > products from mulitple vendors, all use the same product code as well. I have
> > set up 3 different vendors to compare prices on items. How do i put a formula
> > together on my master worksheet to scan these other 3 worksheets that are
> > identical to where i can have my master worksheet return the lowest price? So
> > if I type in product #1234 in the master sheet it will look at Sheet 1:3 and
> > return the lowest priced product?