Data Validation for a Parts List

[DanCook]

Hello All,

I've been struggling to produce some data validation for a parts list/quote tool. I have a large parts list in a table and I'd like to use this data for a quote worksheet. I'd like the data validation in the quote sheet to look at the 'Catagory' and return all 'Item' in the data validation which match the 'Catagory.' Is there a way to do this?

Any help would be appreciated.

Category Item Part Number Manufacturer Conduit Centaur 20mm PVC Round Conduit Heavy Gauge White (3m Length) 0009-1240 Centaur Conduit Centaur 20mm Female Adaptor with Male Bush White 0008-9959 Centaur Conduit Centaur 20mm PVC Conduit Spacer Bar Saddle White 0009-1723 Centaur Conduit Centaur 20mm PVC Conduit Straight Coupler White 0009-0889 Centaur Conduit Centaur 20mm PVC Conduit Inspection Elbow White 0009-0352 Centaur Conduit Centaur 20mm PVC Conduit Slip Bend White 0009-0865 Centaur Conduit Centaur 250ml Waterproof Adhesive Permanent 0009-1621 Centaur DIN Rail Slotted Din Rail, 2m x 35mm x 7.5mm 742-4138 RS Pro Enclosures 300 mm X 300 mm X 120 mm - Terminal boxes KL without gland plate (WxHxD) 1507510 Rittal Enclosures 300 mm X 400 mm X 200 mm - Plastic enclosures KS (WxHxD) 1434500 Rittal Enclosures 300 mm X 300 mm X 120 mm - E-Box EB (WxHxD) Painted Steel 1555500 Rittal Indicators Indicator lights, 22mm, round, metal, shiny, green, smooth lens, with holder, LED module, with integrated LED 24V AC/DC, screw terminal 3SU1152-6AA40-1AA0 Siemens Indicators Indicator lights, 22mm, round, metal, shiny, red, smooth lens, with holder, LED module, with integrated LED 24V AC/DC, screw terminal 3SU1152-6AA20-1AA0 Siemens Indicators Indicator lights, 22 mm, round, metal, shiny, yellow, lens, smooth, with holder, LED module with integrated LED 24 V AC/DC, screw terminal 3SU1152-6AA30-1AA0 Siemens Indicators Indicator lights, 22 mm, round, metal, shiny, white, lens, smooth, with holder, LED module with integrated LED 24 V AC/DC, screw terminal 3SU1152-6AA60-1AA0 Siemens Push Buttons Pushbutton, 22 mm, round, metal, shiny, black, pushbutton, flat, momentary contact type, with holder, 1 NO, screw terminal 3SU1150-0AB10-1BA0 Siemens Push Buttons Pushbutton, 22 mm, round, metal, shiny, green, pushbutton, flat, momentary contact type, 1 NO, screw terminal 3SU1150-0AB40-1BA0 Siemens Category Item Part Number Manufacturer Conduit Centaur 20mm PVC Round Conduit Heavy Gauge White (3m Length) 0009-1240 Centaur Conduit Centaur 20mm Female Adaptor with Male Bush White 0008-9959 Centaur Conduit Centaur 20mm PVC Conduit Spacer Bar Saddle White 0009-1723 Centaur Conduit Centaur 20mm PVC Conduit Straight Coupler White 0009-0889 Centaur Conduit Centaur 20mm PVC Conduit Inspection Elbow White 0009-0352 Centaur Conduit Centaur 20mm PVC Conduit Slip Bend White 0009-0865 Centaur Conduit Centaur 250ml Waterproof Adhesive Permanent 0009-1621 Centaur DIN Rail Slotted Din Rail, 2m x 35mm x 7.5mm 742-4138 RS Pro Enclosures 300 mm X 300 mm X 120 mm - Terminal boxes KL without gland plate (WxHxD) 1507510 Rittal Enclosures 300 mm X 400 mm X 200 mm - Plastic enclosures KS (WxHxD) 1434500 Rittal Enclosures 300 mm X 300 mm X 120 mm - E-Box EB (WxHxD) Painted Steel 1555500 Rittal Indicators Indicator lights, 22mm, round, metal, shiny, green, smooth lens, with holder, LED module, with integrated LED 24V AC/DC, screw terminal 3SU1152-6AA40-1AA0 Siemens Indicators Indicator lights, 22mm, round, metal, shiny, red, smooth lens, with holder, LED module, with integrated LED 24V AC/DC, screw terminal 3SU1152-6AA20-1AA0 Siemens Indicators Indicator lights, 22 mm, round, metal, shiny, yellow, lens, smooth, with holder, LED module with integrated LED 24 V AC/DC, screw terminal 3SU1152-6AA30-1AA0 Siemens Indicators Indicator lights, 22 mm, round, metal, shiny, white, lens, smooth, with holder, LED module with integrated LED 24 V AC/DC, screw terminal 3SU1152-6AA60-1AA0 Siemens Push Buttons Pushbutton, 22 mm, round, metal, shiny, black, pushbutton, flat, momentary contact type, with holder, 1 NO, screw terminal 3SU1150-0AB10-1BA0 Siemens Push Buttons Pushbutton, 22 mm, round, metal, shiny, green, pushbutton, flat, momentary contact type, 1 NO, screw terminal 3SU1150-0AB40-1BA0 Siemens ​ ​ ​

 

[Rijnsent]

Hi Dan,
you can use the new (Office365) FILTER function for that. See e.g. this tutorial on that function: Excel FILTER Function – The Function that does Everything - Excel Unplugged and this use of it: Excel Filter Function + Data Validation
So I would create 2 helper columns (preferably on another sheet):
- =UNIQUE(A2:A100) ->assuming your data starts in A2, this gives a list of all unique categories, which you can use as the validation source, the formula will be something ending with a # (see the second link above)
- =FILTER(B2:$B$13,$A$2:$A$13=$E$3) -> E3 being the cell where your first validation input is -> this gives all items from B that have a matching item in A. Again, use that outcome as the source of your validation.
Hope that works,
Koen