| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to differentiate between product lines
OK, here is my problem. I have to build a pricing spreadsheet to price
products that cross product lines. I need to be able to input a nomenclature from either line and get the pricing for either line. eg: Product 1 Product 2 W3030 W3030 WMGD3030 W3030 WDFMGD3030 W3030 WMGD3030MI W3030 B30ST B30HDS B30DDT B30HDS BDF30FDS BDF30FDS Some items are the same in both product lines, but product line 1 has more options than product line 2. When an item is chosen in product line 1 that is not available in product line 2 I need it to downgrade to the appropriate product. Can anyone help? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to differentiate between product lines
Where does the pricing come in?
Are the first 4 items under Product 2 the same price as their counterparts under Product 1? If so, wouldn't a simple Vlookup formula work? If not, how would you differentiate between them? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... OK, here is my problem. I have to build a pricing spreadsheet to price products that cross product lines. I need to be able to input a nomenclature from either line and get the pricing for either line. eg: Product 1 Product 2 W3030 W3030 WMGD3030 W3030 WDFMGD3030 W3030 WMGD3030MI W3030 B30ST B30HDS B30DDT B30HDS BDF30FDS BDF30FDS Some items are the same in both product lines, but product line 1 has more options than product line 2. When an item is chosen in product line 1 that is not available in product line 2 I need it to downgrade to the appropriate product. Can anyone help? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to differentiate between product lines
Sorry, didn't give enough info...
OK, here is what I am doing. User will input nomenclature into A1(PL1). I then need B1 to look at A1 and pull the nomenclature for PL2 and put it into B1. After this is done I will write out the Index-Match functions that will run off of A1 and B1 appropriately. And to answer your other question. The first 4 item examples in PL1 are not the same price. The first 4 item examples in PL2 are the closest equivalent product to the item in PL1 and are the same price. Thanks for any help you can provide. "RagDyeR" wrote: Where does the pricing come in? Are the first 4 items under Product 2 the same price as their counterparts under Product 1? If so, wouldn't a simple Vlookup formula work? If not, how would you differentiate between them? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... OK, here is my problem. I have to build a pricing spreadsheet to price products that cross product lines. I need to be able to input a nomenclature from either line and get the pricing for either line. eg: Product 1 Product 2 W3030 W3030 WMGD3030 W3030 WDFMGD3030 W3030 WMGD3030MI W3030 B30ST B30HDS B30DDT B30HDS BDF30FDS BDF30FDS Some items are the same in both product lines, but product line 1 has more options than product line 2. When an item is chosen in product line 1 that is not available in product line 2 I need it to downgrade to the appropriate product. Can anyone help? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to differentiate between product lines
I never admitted to be overly bright, but I don't understand what you're
trying to do. According to your explanation, User enters a name that is resident in PL1 - A formula will return the corresponding name from PL2 - And then you will create another formula to match up the PL1 and PL2 names - Didn't the first formula accomplish that last action at the outset? What am I missing ... OR ... what are you not explaining? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bchappell" wrote in message ... Sorry, didn't give enough info... OK, here is what I am doing. User will input nomenclature into A1(PL1). I then need B1 to look at A1 and pull the nomenclature for PL2 and put it into B1. After this is done I will write out the Index-Match functions that will run off of A1 and B1 appropriately. And to answer your other question. The first 4 item examples in PL1 are not the same price. The first 4 item examples in PL2 are the closest equivalent product to the item in PL1 and are the same price. Thanks for any help you can provide. "RagDyeR" wrote: Where does the pricing come in? Are the first 4 items under Product 2 the same price as their counterparts under Product 1? If so, wouldn't a simple Vlookup formula work? If not, how would you differentiate between them? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... OK, here is my problem. I have to build a pricing spreadsheet to price products that cross product lines. I need to be able to input a nomenclature from either line and get the pricing for either line. eg: Product 1 Product 2 W3030 W3030 WMGD3030 W3030 WDFMGD3030 W3030 WMGD3030MI W3030 B30ST B30HDS B30DDT B30HDS BDF30FDS BDF30FDS Some items are the same in both product lines, but product line 1 has more options than product line 2. When an item is chosen in product line 1 that is not available in product line 2 I need it to downgrade to the appropriate product. Can anyone help? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to differentiate between product lines
Sorry about that, I have been working on this so long I forget that it isn't
readily obvious to anyone else. What I am doing is setting up a worksheet that will accept input into A1. B1 will look at A1 and determine what item corresponds to A1 and put it into B1. Then C1-.... will have Index-Match functions that will look at the appropriate cell, A1 or B1, and then the correct DB and pull up the price of the item. EG. A1= W3030 B1 then looks at A1 and determines that W3030 is the same in both lines. B1= W3030 C1 then looks at A1 and Index-Matches to find the price in appropriate DB. C1=333 or A1= WMGD3030 B1 then looks at A1 and since the MGD option is not available in PL2, strips out the MGD and puts W3030 in B1. B1= W3030 C1 Index-Match functions then lookup A1 and puts the price in C1. C1= 400 D1 looks at B1 and Index-Match functions lookup B1 and puts the price in D1. D1= 333. I hope this makes it a little clearer and thanks for any help you can provide. "RagDyer" wrote: I never admitted to be overly bright, but I don't understand what you're trying to do. According to your explanation, User enters a name that is resident in PL1 - A formula will return the corresponding name from PL2 - And then you will create another formula to match up the PL1 and PL2 names - Didn't the first formula accomplish that last action at the outset? What am I missing ... OR ... what are you not explaining? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bchappell" wrote in message ... Sorry, didn't give enough info... OK, here is what I am doing. User will input nomenclature into A1(PL1). I then need B1 to look at A1 and pull the nomenclature for PL2 and put it into B1. After this is done I will write out the Index-Match functions that will run off of A1 and B1 appropriately. And to answer your other question. The first 4 item examples in PL1 are not the same price. The first 4 item examples in PL2 are the closest equivalent product to the item in PL1 and are the same price. Thanks for any help you can provide. "RagDyeR" wrote: Where does the pricing come in? Are the first 4 items under Product 2 the same price as their counterparts under Product 1? If so, wouldn't a simple Vlookup formula work? If not, how would you differentiate between them? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... OK, here is my problem. I have to build a pricing spreadsheet to price products that cross product lines. I need to be able to input a nomenclature from either line and get the pricing for either line. eg: Product 1 Product 2 W3030 W3030 WMGD3030 W3030 WDFMGD3030 W3030 WMGD3030MI W3030 B30ST B30HDS B30DDT B30HDS BDF30FDS BDF30FDS Some items are the same in both product lines, but product line 1 has more options than product line 2. When an item is chosen in product line 1 that is not available in product line 2 I need it to downgrade to the appropriate product. Can anyone help? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to differentiate between product lines
Do you have a complete 2 column datalist of Product1 and it's corresponding
Product2 counterpart? How do you determine the proper database to use for pricing, and where are these Db's located, and how many of them are there? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... Sorry about that, I have been working on this so long I forget that it isn't readily obvious to anyone else. What I am doing is setting up a worksheet that will accept input into A1. B1 will look at A1 and determine what item corresponds to A1 and put it into B1. Then C1-.... will have Index-Match functions that will look at the appropriate cell, A1 or B1, and then the correct DB and pull up the price of the item. EG. A1= W3030 B1 then looks at A1 and determines that W3030 is the same in both lines. B1= W3030 C1 then looks at A1 and Index-Matches to find the price in appropriate DB. C1=333 or A1= WMGD3030 B1 then looks at A1 and since the MGD option is not available in PL2, strips out the MGD and puts W3030 in B1. B1= W3030 C1 Index-Match functions then lookup A1 and puts the price in C1. C1= 400 D1 looks at B1 and Index-Match functions lookup B1 and puts the price in D1. D1= 333. I hope this makes it a little clearer and thanks for any help you can provide. "RagDyer" wrote: I never admitted to be overly bright, but I don't understand what you're trying to do. According to your explanation, User enters a name that is resident in PL1 - A formula will return the corresponding name from PL2 - And then you will create another formula to match up the PL1 and PL2 names - Didn't the first formula accomplish that last action at the outset? What am I missing ... OR ... what are you not explaining? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bchappell" wrote in message ... Sorry, didn't give enough info... OK, here is what I am doing. User will input nomenclature into A1(PL1). I then need B1 to look at A1 and pull the nomenclature for PL2 and put it into B1. After this is done I will write out the Index-Match functions that will run off of A1 and B1 appropriately. And to answer your other question. The first 4 item examples in PL1 are not the same price. The first 4 item examples in PL2 are the closest equivalent product to the item in PL1 and are the same price. Thanks for any help you can provide. "RagDyeR" wrote: Where does the pricing come in? Are the first 4 items under Product 2 the same price as their counterparts under Product 1? If so, wouldn't a simple Vlookup formula work? If not, how would you differentiate between them? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... OK, here is my problem. I have to build a pricing spreadsheet to price products that cross product lines. I need to be able to input a nomenclature from either line and get the pricing for either line. eg: Product 1 Product 2 W3030 W3030 WMGD3030 W3030 WDFMGD3030 W3030 WMGD3030MI W3030 B30ST B30HDS B30DDT B30HDS BDF30FDS BDF30FDS Some items are the same in both product lines, but product line 1 has more options than product line 2. When an item is chosen in product line 1 that is not available in product line 2 I need it to downgrade to the appropriate product. Can anyone help? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to differentiate between product lines
Yes, I have complete datalist of PL1 and PL2. Actually at present I have 3
product lines I am working with, and I know of 2 more that will be added in the future. The DB's will be resident in the workbook, located on different worksheets for each one. Determination of which DB to access is written into the Index-Match functions I am going to use. Once again Thanks for your help. "RagDyeR" wrote: Do you have a complete 2 column datalist of Product1 and it's corresponding Product2 counterpart? How do you determine the proper database to use for pricing, and where are these Db's located, and how many of them are there? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... Sorry about that, I have been working on this so long I forget that it isn't readily obvious to anyone else. What I am doing is setting up a worksheet that will accept input into A1. B1 will look at A1 and determine what item corresponds to A1 and put it into B1. Then C1-.... will have Index-Match functions that will look at the appropriate cell, A1 or B1, and then the correct DB and pull up the price of the item. EG. A1= W3030 B1 then looks at A1 and determines that W3030 is the same in both lines. B1= W3030 C1 then looks at A1 and Index-Matches to find the price in appropriate DB. C1=333 or A1= WMGD3030 B1 then looks at A1 and since the MGD option is not available in PL2, strips out the MGD and puts W3030 in B1. B1= W3030 C1 Index-Match functions then lookup A1 and puts the price in C1. C1= 400 D1 looks at B1 and Index-Match functions lookup B1 and puts the price in D1. D1= 333. I hope this makes it a little clearer and thanks for any help you can provide. "RagDyer" wrote: I never admitted to be overly bright, but I don't understand what you're trying to do. According to your explanation, User enters a name that is resident in PL1 - A formula will return the corresponding name from PL2 - And then you will create another formula to match up the PL1 and PL2 names - Didn't the first formula accomplish that last action at the outset? What am I missing ... OR ... what are you not explaining? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bchappell" wrote in message ... Sorry, didn't give enough info... OK, here is what I am doing. User will input nomenclature into A1(PL1). I then need B1 to look at A1 and pull the nomenclature for PL2 and put it into B1. After this is done I will write out the Index-Match functions that will run off of A1 and B1 appropriately. And to answer your other question. The first 4 item examples in PL1 are not the same price. The first 4 item examples in PL2 are the closest equivalent product to the item in PL1 and are the same price. Thanks for any help you can provide. "RagDyeR" wrote: Where does the pricing come in? Are the first 4 items under Product 2 the same price as their counterparts under Product 1? If so, wouldn't a simple Vlookup formula work? If not, how would you differentiate between them? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... OK, here is my problem. I have to build a pricing spreadsheet to price products that cross product lines. I need to be able to input a nomenclature from either line and get the pricing for either line. eg: Product 1 Product 2 W3030 W3030 WMGD3030 W3030 WDFMGD3030 W3030 WMGD3030MI W3030 B30ST B30HDS B30DDT B30HDS BDF30FDS BDF30FDS Some items are the same in both product lines, but product line 1 has more options than product line 2. When an item is chosen in product line 1 that is not available in product line 2 I need it to downgrade to the appropriate product. Can anyone help? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to differentiate between product lines
If you already have the formulas constructed to pull the pricing from the
various data bases, what exactly do you need help with? To get the matching product name into B1 when a user enters a name in A1? That could be a simple Vlookup formula. Say with the PL1 and PL2 lists on Sheet2, in A1 to B50, And the user enters a name in A1 of Sheet1, This formula could be in B1 to return the matching name: =Vlookup(A1,Sheet2!A1:B50,2,0) Is this what you're looking for? -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bchappell" wrote in message ... Yes, I have complete datalist of PL1 and PL2. Actually at present I have 3 product lines I am working with, and I know of 2 more that will be added in the future. The DB's will be resident in the workbook, located on different worksheets for each one. Determination of which DB to access is written into the Index-Match functions I am going to use. Once again Thanks for your help. "RagDyeR" wrote: Do you have a complete 2 column datalist of Product1 and it's corresponding Product2 counterpart? How do you determine the proper database to use for pricing, and where are these Db's located, and how many of them are there? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... Sorry about that, I have been working on this so long I forget that it isn't readily obvious to anyone else. What I am doing is setting up a worksheet that will accept input into A1. B1 will look at A1 and determine what item corresponds to A1 and put it into B1. Then C1-.... will have Index-Match functions that will look at the appropriate cell, A1 or B1, and then the correct DB and pull up the price of the item. EG. A1= W3030 B1 then looks at A1 and determines that W3030 is the same in both lines. B1= W3030 C1 then looks at A1 and Index-Matches to find the price in appropriate DB. C1=333 or A1= WMGD3030 B1 then looks at A1 and since the MGD option is not available in PL2, strips out the MGD and puts W3030 in B1. B1= W3030 C1 Index-Match functions then lookup A1 and puts the price in C1. C1= 400 D1 looks at B1 and Index-Match functions lookup B1 and puts the price in D1. D1= 333. I hope this makes it a little clearer and thanks for any help you can provide. "RagDyer" wrote: I never admitted to be overly bright, but I don't understand what you're trying to do. According to your explanation, User enters a name that is resident in PL1 - A formula will return the corresponding name from PL2 - And then you will create another formula to match up the PL1 and PL2 names - Didn't the first formula accomplish that last action at the outset? What am I missing ... OR ... what are you not explaining? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bchappell" wrote in message ... Sorry, didn't give enough info... OK, here is what I am doing. User will input nomenclature into A1(PL1). I then need B1 to look at A1 and pull the nomenclature for PL2 and put it into B1. After this is done I will write out the Index-Match functions that will run off of A1 and B1 appropriately. And to answer your other question. The first 4 item examples in PL1 are not the same price. The first 4 item examples in PL2 are the closest equivalent product to the item in PL1 and are the same price. Thanks for any help you can provide. "RagDyeR" wrote: Where does the pricing come in? Are the first 4 items under Product 2 the same price as their counterparts under Product 1? If so, wouldn't a simple Vlookup formula work? If not, how would you differentiate between them? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... OK, here is my problem. I have to build a pricing spreadsheet to price products that cross product lines. I need to be able to input a nomenclature from either line and get the pricing for either line. eg: Product 1 Product 2 W3030 W3030 WMGD3030 W3030 WDFMGD3030 W3030 WMGD3030MI W3030 B30ST B30HDS B30DDT B30HDS BDF30FDS BDF30FDS Some items are the same in both product lines, but product line 1 has more options than product line 2. When an item is chosen in product line 1 that is not available in product line 2 I need it to downgrade to the appropriate product. Can anyone help? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to differentiate between product lines
To get the matching product name into B1 when a user enters a name in A1?
That could be a simple Vlookup formula. True, it is a simple VLookup for the products that correspond. The problem comes in where there is no corresponding product. EG: WMGD3030 is a product in PL1, but it doesn't exist in PL2. When someones enters WMGD3030 I would like the cell to look at PL2 determine if the product is available or not, then if it isn't, find the nearest equivalent and put it into the cell. EG: WMGD3030 becomes W3030. I guess the problem I am having is determining the correct order of events that need to take place for it to do that. Look at A1 Determine if product is available, then either A: use available or B: find equivalent "RagDyer" wrote: If you already have the formulas constructed to pull the pricing from the various data bases, what exactly do you need help with? To get the matching product name into B1 when a user enters a name in A1? That could be a simple Vlookup formula. Say with the PL1 and PL2 lists on Sheet2, in A1 to B50, And the user enters a name in A1 of Sheet1, This formula could be in B1 to return the matching name: =Vlookup(A1,Sheet2!A1:B50,2,0) Is this what you're looking for? -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bchappell" wrote in message ... Yes, I have complete datalist of PL1 and PL2. Actually at present I have 3 product lines I am working with, and I know of 2 more that will be added in the future. The DB's will be resident in the workbook, located on different worksheets for each one. Determination of which DB to access is written into the Index-Match functions I am going to use. Once again Thanks for your help. "RagDyeR" wrote: Do you have a complete 2 column datalist of Product1 and it's corresponding Product2 counterpart? How do you determine the proper database to use for pricing, and where are these Db's located, and how many of them are there? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... Sorry about that, I have been working on this so long I forget that it isn't readily obvious to anyone else. What I am doing is setting up a worksheet that will accept input into A1. B1 will look at A1 and determine what item corresponds to A1 and put it into B1. Then C1-.... will have Index-Match functions that will look at the appropriate cell, A1 or B1, and then the correct DB and pull up the price of the item. EG. A1= W3030 B1 then looks at A1 and determines that W3030 is the same in both lines. B1= W3030 C1 then looks at A1 and Index-Matches to find the price in appropriate DB. C1=333 or A1= WMGD3030 B1 then looks at A1 and since the MGD option is not available in PL2, strips out the MGD and puts W3030 in B1. B1= W3030 C1 Index-Match functions then lookup A1 and puts the price in C1. C1= 400 D1 looks at B1 and Index-Match functions lookup B1 and puts the price in D1. D1= 333. I hope this makes it a little clearer and thanks for any help you can provide. "RagDyer" wrote: I never admitted to be overly bright, but I don't understand what you're trying to do. According to your explanation, User enters a name that is resident in PL1 - A formula will return the corresponding name from PL2 - And then you will create another formula to match up the PL1 and PL2 names - Didn't the first formula accomplish that last action at the outset? What am I missing ... OR ... what are you not explaining? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bchappell" wrote in message ... Sorry, didn't give enough info... OK, here is what I am doing. User will input nomenclature into A1(PL1). I then need B1 to look at A1 and pull the nomenclature for PL2 and put it into B1. After this is done I will write out the Index-Match functions that will run off of A1 and B1 appropriately. And to answer your other question. The first 4 item examples in PL1 are not the same price. The first 4 item examples in PL2 are the closest equivalent product to the item in PL1 and are the same price. Thanks for any help you can provide. "RagDyeR" wrote: Where does the pricing come in? Are the first 4 items under Product 2 the same price as their counterparts under Product 1? If so, wouldn't a simple Vlookup formula work? If not, how would you differentiate between them? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... OK, here is my problem. I have to build a pricing spreadsheet to price products that cross product lines. I need to be able to input a nomenclature from either line and get the pricing for either line. eg: Product 1 Product 2 W3030 W3030 WMGD3030 W3030 WDFMGD3030 W3030 WMGD3030MI W3030 B30ST B30HDS B30DDT B30HDS BDF30FDS BDF30FDS Some items are the same in both product lines, but product line 1 has more options than product line 2. When an item is chosen in product line 1 that is not available in product line 2 I need it to downgrade to the appropriate product. Can anyone help? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to differentiate between product lines
In your example in your OP, you displayed alternate options in PL2 on the
same row as the full named product in PL1. Isn't that how your datalist is constructed? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... To get the matching product name into B1 when a user enters a name in A1? That could be a simple Vlookup formula. True, it is a simple VLookup for the products that correspond. The problem comes in where there is no corresponding product. EG: WMGD3030 is a product in PL1, but it doesn't exist in PL2. When someones enters WMGD3030 I would like the cell to look at PL2 determine if the product is available or not, then if it isn't, find the nearest equivalent and put it into the cell. EG: WMGD3030 becomes W3030. I guess the problem I am having is determining the correct order of events that need to take place for it to do that. Look at A1 Determine if product is available, then either A: use available or B: find equivalent "RagDyer" wrote: If you already have the formulas constructed to pull the pricing from the various data bases, what exactly do you need help with? To get the matching product name into B1 when a user enters a name in A1? That could be a simple Vlookup formula. Say with the PL1 and PL2 lists on Sheet2, in A1 to B50, And the user enters a name in A1 of Sheet1, This formula could be in B1 to return the matching name: =Vlookup(A1,Sheet2!A1:B50,2,0) Is this what you're looking for? -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bchappell" wrote in message ... Yes, I have complete datalist of PL1 and PL2. Actually at present I have 3 product lines I am working with, and I know of 2 more that will be added in the future. The DB's will be resident in the workbook, located on different worksheets for each one. Determination of which DB to access is written into the Index-Match functions I am going to use. Once again Thanks for your help. "RagDyeR" wrote: Do you have a complete 2 column datalist of Product1 and it's corresponding Product2 counterpart? How do you determine the proper database to use for pricing, and where are these Db's located, and how many of them are there? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... Sorry about that, I have been working on this so long I forget that it isn't readily obvious to anyone else. What I am doing is setting up a worksheet that will accept input into A1. B1 will look at A1 and determine what item corresponds to A1 and put it into B1. Then C1-.... will have Index-Match functions that will look at the appropriate cell, A1 or B1, and then the correct DB and pull up the price of the item. EG. A1= W3030 B1 then looks at A1 and determines that W3030 is the same in both lines. B1= W3030 C1 then looks at A1 and Index-Matches to find the price in appropriate DB. C1=333 or A1= WMGD3030 B1 then looks at A1 and since the MGD option is not available in PL2, strips out the MGD and puts W3030 in B1. B1= W3030 C1 Index-Match functions then lookup A1 and puts the price in C1. C1= 400 D1 looks at B1 and Index-Match functions lookup B1 and puts the price in D1. D1= 333. I hope this makes it a little clearer and thanks for any help you can provide. "RagDyer" wrote: I never admitted to be overly bright, but I don't understand what you're trying to do. According to your explanation, User enters a name that is resident in PL1 - A formula will return the corresponding name from PL2 - And then you will create another formula to match up the PL1 and PL2 names - Didn't the first formula accomplish that last action at the outset? What am I missing ... OR ... what are you not explaining? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bchappell" wrote in message ... Sorry, didn't give enough info... OK, here is what I am doing. User will input nomenclature into A1(PL1). I then need B1 to look at A1 and pull the nomenclature for PL2 and put it into B1. After this is done I will write out the Index-Match functions that will run off of A1 and B1 appropriately. And to answer your other question. The first 4 item examples in PL1 are not the same price. The first 4 item examples in PL2 are the closest equivalent product to the item in PL1 and are the same price. Thanks for any help you can provide. "RagDyeR" wrote: Where does the pricing come in? Are the first 4 items under Product 2 the same price as their counterparts under Product 1? If so, wouldn't a simple Vlookup formula work? If not, how would you differentiate between them? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... OK, here is my problem. I have to build a pricing spreadsheet to price products that cross product lines. I need to be able to input a nomenclature from either line and get the pricing for either line. eg: Product 1 Product 2 W3030 W3030 WMGD3030 W3030 WDFMGD3030 W3030 WMGD3030MI W3030 B30ST B30HDS B30DDT B30HDS BDF30FDS BDF30FDS Some items are the same in both product lines, but product line 1 has more options than product line 2. When an item is chosen in product line 1 that is not available in product line 2 I need it to downgrade to the appropriate product. Can anyone help? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to differentiate between product lines
No, that is part of the problem. I have to build this on the premise that
each year when we receive new DB's, we can just plug them into the appropriate page and everything will continue to work as normal. These DB's are provided by the manufacturers of the different products. Because of this I cannot reformat the DB's. If it wasn't for this issue I wouldn't have a problem. I could just reformat the DB's as required. The problem is no one here (myself included) is really adept at Excel. I am probably the most adept of anyone here. Which is why that brought this to me. "RagDyeR" wrote: In your example in your OP, you displayed alternate options in PL2 on the same row as the full named product in PL1. Isn't that how your datalist is constructed? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... To get the matching product name into B1 when a user enters a name in A1? That could be a simple Vlookup formula. True, it is a simple VLookup for the products that correspond. The problem comes in where there is no corresponding product. EG: WMGD3030 is a product in PL1, but it doesn't exist in PL2. When someones enters WMGD3030 I would like the cell to look at PL2 determine if the product is available or not, then if it isn't, find the nearest equivalent and put it into the cell. EG: WMGD3030 becomes W3030. I guess the problem I am having is determining the correct order of events that need to take place for it to do that. Look at A1 Determine if product is available, then either A: use available or B: find equivalent "RagDyer" wrote: If you already have the formulas constructed to pull the pricing from the various data bases, what exactly do you need help with? To get the matching product name into B1 when a user enters a name in A1? That could be a simple Vlookup formula. Say with the PL1 and PL2 lists on Sheet2, in A1 to B50, And the user enters a name in A1 of Sheet1, This formula could be in B1 to return the matching name: =Vlookup(A1,Sheet2!A1:B50,2,0) Is this what you're looking for? -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bchappell" wrote in message ... Yes, I have complete datalist of PL1 and PL2. Actually at present I have 3 product lines I am working with, and I know of 2 more that will be added in the future. The DB's will be resident in the workbook, located on different worksheets for each one. Determination of which DB to access is written into the Index-Match functions I am going to use. Once again Thanks for your help. "RagDyeR" wrote: Do you have a complete 2 column datalist of Product1 and it's corresponding Product2 counterpart? How do you determine the proper database to use for pricing, and where are these Db's located, and how many of them are there? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... Sorry about that, I have been working on this so long I forget that it isn't readily obvious to anyone else. What I am doing is setting up a worksheet that will accept input into A1. B1 will look at A1 and determine what item corresponds to A1 and put it into B1. Then C1-.... will have Index-Match functions that will look at the appropriate cell, A1 or B1, and then the correct DB and pull up the price of the item. EG. A1= W3030 B1 then looks at A1 and determines that W3030 is the same in both lines. B1= W3030 C1 then looks at A1 and Index-Matches to find the price in appropriate DB. C1=333 or A1= WMGD3030 B1 then looks at A1 and since the MGD option is not available in PL2, strips out the MGD and puts W3030 in B1. B1= W3030 C1 Index-Match functions then lookup A1 and puts the price in C1. C1= 400 D1 looks at B1 and Index-Match functions lookup B1 and puts the price in D1. D1= 333. I hope this makes it a little clearer and thanks for any help you can provide. "RagDyer" wrote: I never admitted to be overly bright, but I don't understand what you're trying to do. According to your explanation, User enters a name that is resident in PL1 - A formula will return the corresponding name from PL2 - And then you will create another formula to match up the PL1 and PL2 names - Didn't the first formula accomplish that last action at the outset? What am I missing ... OR ... what are you not explaining? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bchappell" wrote in message ... Sorry, didn't give enough info... OK, here is what I am doing. User will input nomenclature into A1(PL1). I then need B1 to look at A1 and pull the nomenclature for PL2 and put it into B1. After this is done I will write out the Index-Match functions that will run off of A1 and B1 appropriately. And to answer your other question. The first 4 item examples in PL1 are not the same price. The first 4 item examples in PL2 are the closest equivalent product to the item in PL1 and are the same price. Thanks for any help you can provide. "RagDyeR" wrote: Where does the pricing come in? Are the first 4 items under Product 2 the same price as their counterparts under Product 1? If so, wouldn't a simple Vlookup formula work? If not, how would you differentiate between them? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... OK, here is my problem. I have to build a pricing spreadsheet to price products that cross product lines. I need to be able to input a nomenclature from either line and get the pricing for either line. eg: Product 1 Product 2 W3030 W3030 WMGD3030 W3030 WDFMGD3030 W3030 WMGD3030MI W3030 B30ST B30HDS B30DDT B30HDS BDF30FDS BDF30FDS Some items are the same in both product lines, but product line 1 has more options than product line 2. When an item is chosen in product line 1 that is not available in product line 2 I need it to downgrade to the appropriate product. Can anyone help? |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to differentiate between product lines
SO ... your example of PL1 and PL2 is *not* accurate!
Let's start from the beginning. Can you post exact examples of the 2 lists? Are they paired up in adjoining columns or not? OR, are they 2 completely separate entities, with no *physical* connection with each other. For example, could PL1 be in A20 to A50, And PL2 in A60 to A75? What is the determining factor that creates (accepts) a match of *unidentical* names. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bchappell" wrote in message ... OK, here is my problem. I have to build a pricing spreadsheet to price products that cross product lines. I need to be able to input a nomenclature from either line and get the pricing for either line. eg: Product 1 Product 2 W3030 W3030 WMGD3030 W3030 WDFMGD3030 W3030 WMGD3030MI W3030 B30ST B30HDS B30DDT B30HDS BDF30FDS BDF30FDS Some items are the same in both product lines, but product line 1 has more options than product line 2. When an item is chosen in product line 1 that is not available in product line 2 I need it to downgrade to the appropriate product. Can anyone help? |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to differentiate between product lines
RagDyer thanks for your assistance. I decided to do this a different way. I
created a conversion matrix for the different product lines and used this to tie everything together. With over 5,000 different sku's it wasn't easy but it is working now. Of course when the new product lines are added I may have to think of something else to make everything work together. Again thanks for all your time. Barron "RagDyer" wrote: SO ... your example of PL1 and PL2 is *not* accurate! Let's start from the beginning. Can you post exact examples of the 2 lists? Are they paired up in adjoining columns or not? OR, are they 2 completely separate entities, with no *physical* connection with each other. For example, could PL1 be in A20 to A50, And PL2 in A60 to A75? What is the determining factor that creates (accepts) a match of *unidentical* names. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bchappell" wrote in message ... OK, here is my problem. I have to build a pricing spreadsheet to price products that cross product lines. I need to be able to input a nomenclature from either line and get the pricing for either line. eg: Product 1 Product 2 W3030 W3030 WMGD3030 W3030 WDFMGD3030 W3030 WMGD3030MI W3030 B30ST B30HDS B30DDT B30HDS BDF30FDS BDF30FDS Some items are the same in both product lines, but product line 1 has more options than product line 2. When an item is chosen in product line 1 that is not available in product line 2 I need it to downgrade to the appropriate product. Can anyone help? |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to differentiate between product lines
Appreciate the feed-back.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "bchappell" wrote in message ... RagDyer thanks for your assistance. I decided to do this a different way. I created a conversion matrix for the different product lines and used this to tie everything together. With over 5,000 different sku's it wasn't easy but it is working now. Of course when the new product lines are added I may have to think of something else to make everything work together. Again thanks for all your time. Barron "RagDyer" wrote: SO ... your example of PL1 and PL2 is *not* accurate! Let's start from the beginning. Can you post exact examples of the 2 lists? Are they paired up in adjoining columns or not? OR, are they 2 completely separate entities, with no *physical* connection with each other. For example, could PL1 be in A20 to A50, And PL2 in A60 to A75? What is the determining factor that creates (accepts) a match of *unidentical* names. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bchappell" wrote in message ... OK, here is my problem. I have to build a pricing spreadsheet to price products that cross product lines. I need to be able to input a nomenclature from either line and get the pricing for either line. eg: Product 1 Product 2 W3030 W3030 WMGD3030 W3030 WDFMGD3030 W3030 WMGD3030MI W3030 B30ST B30HDS B30DDT B30HDS BDF30FDS BDF30FDS Some items are the same in both product lines, but product line 1 has more options than product line 2. When an item is chosen in product line 1 that is not available in product line 2 I need it to downgrade to the appropriate product. Can anyone help? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| More lines in Product Pricing Calculator | New Users to Excel | |||
| How do I differentiate between duplicate ranks? | Excel Worksheet Functions | |||
| Differentiate between Number and Date? | Excel Discussion (Misc queries) | |||
| how differentiate between 3 closely spaced lines in excel graph | Charts and Charting in Excel | |||
| Can excel differentiate between zero (tallied) and zero fill (no . | Excel Discussion (Misc queries) | |||
Linear Mode
