Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i retreive data from another worksheet to a current one?
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,Supp lier2,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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i retreive data from another worksheet to a current one?
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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i retreive data from another worksheet to a current one?
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i retreive data from another worksheet to a current one
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look up Data from Worksheet within same Workbook | Excel Discussion (Misc queries) | |||
Look up Data from Worksheet within same Workbook | New Users to Excel | |||
Retreive data from another worksheet based on multiple columns | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Pull Current Month's Data Out of List - Repost | Excel Discussion (Misc queries) |