I am creating a quote form for the sales team. I am using named lists and data validation. Scenario is:
Category Chassis_PN Chassis_Desc
Chassis P2R-8 8 Slot Chassis (Includes Variable PSU & SG/SW)
Power Supply P2R-14 14 Slot Chassis (Includes Variable PSU & SG/SW)
Circuit Cards
Currently:
Cell A1 uses data validation to select from named list "Category". E.g. displays Chassis
Cell B1 uses indirect data validation to select from a named list depending on the category selected. E.g. select P2R-8
Cell C1 auto-populates with the Chassis_Desc, depending on the Chassis_PN selected
All works fine; 40 categories and 400 dependent part numbers (_PN) and associated descriptions (_DESC)
Even managed to pull the pricing into the quote form and dependent on the Chassis_PN selected
Problem: Depending on the Category selected, some of the part numbers (B1) are vague or it is not easy to determine what the part actually is without a description. Currently, the description (C1) is automatically filled in when the part number (B1) is selected. The sales team asked if there was a way to create drop-downs in both (B1) and (C1). Doing this would allow them to scroll either list and select by part number (B1) or by description (C1) to find the item they are looking for.
If the sales guy is scrolling through the part number list (B1), I would like for (C1) to auto-populate on the selection of (B1).
I would also like the reverse, for part number (B1) to auto-populate depending on the selection of description C1.
I've tried indirect validation to (A1) in both (B1) and (C1), but receive circular reference errors.
E.g. If (B1) is empty, activate description list (C1) depending on (A1) selection. If (C1) is empty, activate part number list (B1) depending on (A1) selection.
Am I going about this the right way or is there another option.. without having to write code?
This is my first ever post to a forum. Sorry if I screw something up.
<style type="text/css"> img.imageResizerActiveClass{cursor:nw-resize !important;outline:1px dashed black !important;} img.imageResizerChangedClass{z-index:300 !important;max-width:none !important;max-height:none !important;} img.imageResizerBoxClass{margin:auto; z-index:99999 !important; position:fixed; top:0; left:0; right:0; bottom:0; border:1px solid white; outline:1px solid black;} </style>Excel 2010
<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
<tbody>
</tbody>
<tbody>
</tbody>
<style type="text/css"> img.imageResizerActiveClass{cursor:nw-resize !important;outline:1px dashed black !important;} img.imageResizerChangedClass{z-index:300 !important;max-width:none !important;max-height:none !important;} img.imageResizerBoxClass{margin:auto; z-index:99999 !important; position:fixed; top:0; left:0; right:0; bottom:0; border:1px solid white; outline:1px solid black;} </style>
Category Chassis_PN Chassis_Desc
Chassis P2R-8 8 Slot Chassis (Includes Variable PSU & SG/SW)
Power Supply P2R-14 14 Slot Chassis (Includes Variable PSU & SG/SW)
Circuit Cards
Currently:
Cell A1 uses data validation to select from named list "Category". E.g. displays Chassis
Cell B1 uses indirect data validation to select from a named list depending on the category selected. E.g. select P2R-8
Cell C1 auto-populates with the Chassis_Desc, depending on the Chassis_PN selected
All works fine; 40 categories and 400 dependent part numbers (_PN) and associated descriptions (_DESC)
Even managed to pull the pricing into the quote form and dependent on the Chassis_PN selected
Problem: Depending on the Category selected, some of the part numbers (B1) are vague or it is not easy to determine what the part actually is without a description. Currently, the description (C1) is automatically filled in when the part number (B1) is selected. The sales team asked if there was a way to create drop-downs in both (B1) and (C1). Doing this would allow them to scroll either list and select by part number (B1) or by description (C1) to find the item they are looking for.
If the sales guy is scrolling through the part number list (B1), I would like for (C1) to auto-populate on the selection of (B1).
I would also like the reverse, for part number (B1) to auto-populate depending on the selection of description C1.
I've tried indirect validation to (A1) in both (B1) and (C1), but receive circular reference errors.
E.g. If (B1) is empty, activate description list (C1) depending on (A1) selection. If (C1) is empty, activate part number list (B1) depending on (A1) selection.
Am I going about this the right way or is there another option.. without having to write code?
This is my first ever post to a forum. Sorry if I screw something up.
<style type="text/css"> img.imageResizerActiveClass{cursor:nw-resize !important;outline:1px dashed black !important;} img.imageResizerChangedClass{z-index:300 !important;max-width:none !important;max-height:none !important;} img.imageResizerBoxClass{margin:auto; z-index:99999 !important; position:fixed; top:0; left:0; right:0; bottom:0; border:1px solid white; outline:1px solid black;} </style>Excel 2010
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Column A dropdown refences Master Lists tab | ||||
| 2 | Column B dropdown is dependant on Column A selection and also refences Master Lists tab | ||||
| 3 | Column C looks at the entry in column B and finds the matching entry on the New Price List and returns the adjecent or "Description" entry | ||||
| 4 | Column D does the same as column C, but returns the next adjecent or "List Price" entry | ||||
| 5 | |||||
| 6 | Breakdown of VLOOKUP formula in cell C18 thru C33: | ||||
| 7 | B9 | Compare cell B to table array (New Price List tab) | VLOOKUP(B9,PriceList,2,0) | ||
| 8 | NewPriceList | Table array Name Reference (A9:D498) | VLOOKUP(B9,NewPriceList,2,0) | ||
| 9 | 2 | second column of table array to pull text from | VLOOKUP(B9,PriceList,2,0) | ||
| 10 | 0 | Must be an exact match | VLOOKUP(B9,PriceList,2,0) | ||
| 11 | IFERROR | Hides the "#NA" error if cell A or B is empty | |||
| 12 | Breakdown of VLOOKUP formula in cell D18 thru D33: | ||||
| 13 | All same but change to column 4 of NewPriceList array | ||||
| 14 | |||||
| 15 | 2 | ? Number of systems | |||
| 16 | |||||
| 17 | Select Main List | Select Item | ( Auto-filled ) | Qty | ( Auto-filled ) |
| 18 | |||||
| 19 | PPCards | P2D | Driver Card (48 Channels) | 2 | $ 22,000.00 |
| 20 | |||||
| 21 | P2RUpgrade | P2R-8UG+VTC | PinPoint 2R - 8 slot system (Includes VPS, VTC and TestVue) | 1 | $ 29,000.00 |
| 22 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sample Quote Sheet
Worksheet Formulas
<thead> </thead><tbody> </tbody> |
<tbody>
</tbody>
Workbook Defined Names
<thead> </thead><tbody> </tbody> |
<tbody>
</tbody>
<style type="text/css"> img.imageResizerActiveClass{cursor:nw-resize !important;outline:1px dashed black !important;} img.imageResizerChangedClass{z-index:300 !important;max-width:none !important;max-height:none !important;} img.imageResizerBoxClass{margin:auto; z-index:99999 !important; position:fixed; top:0; left:0; right:0; bottom:0; border:1px solid white; outline:1px solid black;} </style>
