Sorting or Searching?
I am still designing a purchase order form for automotive sales and now have
a new question. For example, if I have a ZPP Premium Package that includes a list of options that has its own codes, i.e. ZPP = 319, 4NA, 430, 431, 403..... how can I get Excel to automatically pick up ZPP if I receive a full list that have other options? For example, I have an automobile that has the following list of options: 205, 465, 319, 203, 4NA, 4AB, 430, 431, 639 How can I get Excel to recognize that ZPP is part of this list? Your assistance (again) is greatly appreciated. -- Erwin |
Sorting or Searching?
Here's one way. It assumes the option codes are entered in the range
B1:B9, which you'll need to change to match your spreadsheet. This formula assumes that numbers such as 319 and 403 are entered as numbers, not text. If they are entered as text, use the second version of the formula. The formula results in TRUE or FALSE: if all 5 constituent codes for ZPP are present, the formula results in TRUE. You can use that in an IF statement to handle the ZPP situation as appropriate. =AND(SUMPRODUCT(--(B1:B9=319))=1,SUMPRODUCT(--(B1:B9="4NA"))=1,SUMPRODUCT(--(B1:B9=430))=1,SUMPRODUCT(--(B1:B9=431))=1,SUMPRODUCT(--(B1:B9=403))=1) If option code numbers are entered as text: =AND(SUMPRODUCT(--(B1:B9="319"))=1,SUMPRODUCT(--(B1:B9="4NA"))=1,SUMPRODUCT(--(B1:B9="430"))=1,SUMPRODUCT(--(B1:B9="431"))=1,SUMPRODUCT(--(B1:B9="403"))=1) |
All times are GMT +1. The time now is 07:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com