Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying text value based on long (comlex) calculation
I am not sure how to even search for what I want to do, so please forgive me if my first post has been discussed and resolved ad naseum in the past... Here is my situation: - I have a workbook that contains multiple sheets. - Each sheet contains auto parts from different suppliers, sorted by part number. - Some suppliers have parts that overlap with each other and the various sheets are updated as pricing and availabilty of parts change. - I am using a combination of the VLOOKUP and MIN functions to determine which supplier has lowest cost based on a comparison of part numbers and price across each supplier. - The first sheet is a summary and contains all of the part numbers (entered manually), as well as the lowest cost from any supplier's worksheet (as calculated above). - Cell A1 of each supplier sheet has the company name in it. My question is this: - For each part number listed on the summary page, I want to display the name of the supplier who has the lowest cost for the part in question. Thanks in advance... -- gezuvor ------------------------------------------------------------------------ gezuvor's Profile: http://www.excelforum.com/member.php...o&userid=30383 View this thread: http://www.excelforum.com/showthread...hreadid=500452 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying text value based on long (comlex) calculation
Have you combined all the Vlookup functions into one formula, or are you
comparing the results of formulas in multiple cells on your summary sheet and chosing the lowest value? If the particular value you use in the summary is in a cell related only to one sheet, then you can use an IF function that says if a particualar cell has the minimum value then the result is equal to A1 on a sheet. "gezuvor" wrote: I am not sure how to even search for what I want to do, so please forgive me if my first post has been discussed and resolved ad naseum in the past... Here is my situation: - I have a workbook that contains multiple sheets. - Each sheet contains auto parts from different suppliers, sorted by part number. - Some suppliers have parts that overlap with each other and the various sheets are updated as pricing and availabilty of parts change. - I am using a combination of the VLOOKUP and MIN functions to determine which supplier has lowest cost based on a comparison of part numbers and price across each supplier. - The first sheet is a summary and contains all of the part numbers (entered manually), as well as the lowest cost from any supplier's worksheet (as calculated above). - Cell A1 of each supplier sheet has the company name in it. My question is this: - For each part number listed on the summary page, I want to display the name of the supplier who has the lowest cost for the part in question. Thanks in advance... -- gezuvor ------------------------------------------------------------------------ gezuvor's Profile: http://www.excelforum.com/member.php...o&userid=30383 View this thread: http://www.excelforum.com/showthread...hreadid=500452 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying text value based on long (comlex) calculation
I have combined all of the VLOOKUP calculations into one formula, but I like your idea. It's not as elegant, but it would be a quick way to accomplish my goal. Here is the long formula, genericized for ease of reading... =IF(ISERROR(MAX(VLOOKUP($B6,'Supplier1'!$B:$D,2,FA LSE),(VLOOKUP($B6,'Supplier2'!$B:$D,2,FALSE)))*'DA TA - General'!$C$3),"",(MAX(VLOOKUP($B6,'Supplier1'!$B: $D,2,FALSE),(VLOOKUP($B6,'Supplier2'!$B:$D,2,FALSE ))))*'DATA - General'!$C$3) I am struggling to find a way to extract the supplier (worksheet) name from this formula to do the relatively simple look-up required to include "Supplier1" in an adjacent cell... :( -- gezuvor ------------------------------------------------------------------------ gezuvor's Profile: http://www.excelforum.com/member.php...o&userid=30383 View this thread: http://www.excelforum.com/showthread...hreadid=500452 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell text based on 4 condition test | Excel Worksheet Functions | |||
advanced filter - can't match a long text cell | Excel Discussion (Misc queries) | |||
Text not copying as text from Word to Excel 2003 | Excel Discussion (Misc queries) | |||
Putting text in a column based on variable text from another colum | Excel Discussion (Misc queries) | |||
returning a text cell based on a number cell | Excel Worksheet Functions |