How do I create COMPLEX indirect data validation lists...

[calumbus53]

Hi guys, please help...

I am trying to create a series of indirect lists but only lists that link to their relative tree.

If say for example Parent "a" was selected, I would only have access to values directly linked with "a" (such as "a1.1, a1.2, a1.3, a1.4, a1.4", "a2.1, a2.2" and so on) and then on into child 2, repeating the same method. If "b" was selected, I only want to be able to select "b" related children, and then again onto the next level down.

I have managed to do this by creating lots of indirect lists but it is manual and just created over a "drag n drop" array! If the data is updated, I'm screwed!

Do you have any ideas? / Fancy help me doing it?

Below is an example of the format I am working to:

[TABLE="width: 730"]
<tbody>[TR]
[TD][/TD]
[TD]Parent Selection[/TD]
[TD]1 Child Selection[/TD]
[TD]2 Child Selection[/TD]
[TD][/TD]
[TD][/TD]
[TD]Parent[/TD]
[TD][/TD]
[TD]1st Child[/TD]
[TD][/TD]
[TD]2 Child[/TD]
[/TR]
[TR]
[TD]10.02.02[/TD]
[TD]a[/TD]
[TD]a2.1[/TD]
[TD]aa2.1[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10.03.03[/TD]
[TD]a[/TD]
[TD]a3.1[/TD]
[TD]aa3.1[/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD]01[/TD]
[TD]a1.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11.02.02[/TD]
[TD]b[/TD]
[TD]b2.2[/TD]
[TD]bb2.1[/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD] [/TD]
[TD]a1.2[/TD]
[TD]01[/TD]
[TD]aa1.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD] [/TD]
[TD]a1.3[/TD]
[TD][/TD]
[TD]aa1.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD] [/TD]
[TD]a1.4[/TD]
[TD][/TD]
[TD]aa1.3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD] [/TD]
[TD]a1.5[/TD]
[TD] [/TD]
[TD]aa1.4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD]02[/TD]
[TD]a2.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD] [/TD]
[TD]a2.2[/TD]
[TD]02[/TD]
[TD]aa2.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD]03[/TD]
[TD]a3.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD] [/TD]
[TD]a3.2[/TD]
[TD]03[/TD]
[TD]aa3.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD] [/TD]
[TD]a3.3[/TD]
[TD] [/TD]
[TD]aa3.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD]04[/TD]
[TD]a4.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD] [/TD]
[TD]a4.2[/TD]
[TD]04[/TD]
[TD]aa4.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD]01[/TD]
[TD]b1.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD] [/TD]
[TD]b1.2[/TD]
[TD]01[/TD]
[TD]bb1.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD]02[/TD]
[TD]b2.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD][/TD]
[TD]b2.2[/TD]
[TD]02[/TD]
[TD]bb2.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD] [/TD]
[TD]b2.3[/TD]
[TD] [/TD]
[TD]bb2.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD]03[/TD]
[TD]b3.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD] [/TD]
[TD]b3.2[/TD]
[TD]03[/TD]
[TD]bb3.1[/TD]
[/TR]
</tbody><colgroup><col><col><col span="2"><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[MickG]

Is it your intention to select 3 values from one of the sets of 3, validation lists values, based on their Interdependence as shown on the Table, and then return the column value as shown below as "Results".

[TABLE="width: 208"]
<colgroup><col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3214;"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2104;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2218;"> <col width="66" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2332;"> <tbody>[TR]
[TD="class: xl66, width: 90, bgcolor: transparent"]Results[/TD]
[TD="class: xl66, width: 59, bgcolor: transparent"]Val1[/TD]
[TD="class: xl66, width: 62, bgcolor: transparent"]Val2[/TD]
[TD="class: xl66, width: 66, bgcolor: transparent"]Val3[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 90, bgcolor: transparent"]10.02.02[/TD]
[TD="class: xl67, width: 59, bgcolor: #FFCC99"]a[/TD]
[TD="class: xl67, width: 62, bgcolor: #FFCC99"]a2.1[/TD]
[TD="class: xl67, width: 66, bgcolor: #FFCC99"]aa2.1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 90, bgcolor: transparent"]10.03.03[/TD]
[TD="class: xl67, width: 59, bgcolor: #FFCC99"]a[/TD]
[TD="class: xl67, width: 62, bgcolor: #FFCC99"]a3.1[/TD]
[TD="class: xl67, width: 66, bgcolor: #FFCC99"]aa3.1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 90, bgcolor: transparent"]11.02.02[/TD]
[TD="class: xl67, width: 59, bgcolor: #FFCC99"]b[/TD]
[TD="class: xl67, width: 62, bgcolor: #FFCC99"]b2.2[/TD]
[TD="class: xl67, width: 66, bgcolor: #FFCC99"]bb2.1[/TD]
[/TR]
</tbody>[/TABLE]

The problem I have with this is that values "a2.1" and "a3.1" do not have a value in the column "Child 2",
and the table itself is not particularly user friendly.

Please explain further !!
Regrds Mick

 

[calumbus53]

Hi Mick,

Thank you for your reply.

I knew this would be very difficult to explain.

I need to be able to select dependencies based on the other selected.


For example:

If I was working with say an car manufacturer.

They have a number of main departments (the "chief"), 2 of which would be Chassis and Powertrain. If you select "Chassis" (as the "chief"), you would have a number of "PMT" groups underneath ("Subframes", "Suspension Systems", "Steering" - to name a few) and then likewise for "Powertrain" ("Engines", "Driveline System" etc) these are what are know as the "PMT's" and then under EACH of these there are "Commodity Groups", which are then above the "Commodities" themselves! - this is because the car is made up of THOUSANDS of commodities and systems.

To look these commodities up independently I need to create a list that is based on the previous selection, eg. "POWERTRAIN > ENGINES > EVAPORATIVE EMISSIONS > VENTING DEVICES AND VALVES" (if I selected "CHASSIS" @ the chief level, I wouldn't be able to choose from the "ENGINES" menu and therefore not cascade through these.

Does this explain it any better? - I understand if not.

Thank you for any help in advance.

Cal

 

[MickG]

Thank you for your explanation, but in truth what I need to know are the answers to the following:-
Q(1) Are you able to accept a VBA solution.
Q(2) How many sets of (3) validation boxes do you want.
Q(4) Are you able to show the data Table in a more user friendly way i.e. with the Blank Cells showing their actual related data.
Q(5) Could the Results be Created with Comboboxes on a Userform,. That way you only need 3 comboboxes then transfer the results to the sheet

Q(6) Would there be any more columns to the table.

Regrds Mick

 

[calumbus53]

Thank you for your explanation, but in truth what I need to know are the answers to the following:-
Q(1) Are you able to accept a VBA solution.
Q(2) How many sets of (3) validation boxes do you want.
Q(4) Are you able to show the data Table in a more user friendly way i.e. with the Blank Cells showing their actual related data.
Q(5) Could the Results be Created with Comboboxes on a Userform,. That way you only need 3 comboboxes then transfer the results to the sheet

Q(6) Would there be any more columns to the table.

Regrds Mick

Hi Mick,

Thank you again for your reply.

A VBA solution would be great and I am trying to generate a user form which could also deliver this information but I just do not have the knowledge and know how.

I would require 4 validation boxes: one at Tier 1, Tier 2, Tier 3, Tier 4 - I have already created validation lists with independent decencies on one an other, however because this was just essentially "auto manipulating" the data when you select a Tier, the same process could not be replicated down through rows unless many many replicas of the lookup data were created.

I would be potentially adding more columns at some point in order to include a description of some sort by the user.

I basically want to avoid a huge list at Tier 3/Tier 4 levels.
If say Tier 1 had the choice of "CAR" or "ANIMAL" then we choose animal, within the second Tier list I would want to see the likes of "Horse", "Cow", etc... NOT "Volvo", "BMW" Tier 3 would then be "Mare", "Stallion" and then Tier 4... the colour selection or something like that.

This method is going to potentially be the process to add data to hundreds / thousands of rows!

Thank you in advance.

Cheers,

Cal

 

[MickG]

Thanks for that Cal .
What are your thoughts on Q(4) regarding the table layout.
If you can show an example of the Table (Not a picture) with all the expected columns, and modified if possible to reflect q(4), that would be very helpful.

My idea is to show 4 ? comboboxes , whose lists will reflect the previous combobox Selections/Choice from the Table i.e. "a, a2.1,aa2.1,??"
And when all 4 boxes are complete will fill a text box with the related result i.e "10,01,01"
When completed you can decide what you want to do with the results !!
Does that sound acceptable
Regrds Mick

 

[calumbus53]

[TABLE="width: 483"]
<colgroup><col width="105" style="width: 79pt; mso-width-source: userset; mso-width-alt: 3840;"> <col width="21" style="width: 16pt; mso-width-source: userset; mso-width-alt: 768;"> <col width="166" style="width: 125pt; mso-width-source: userset; mso-width-alt: 6070;"> <col width="21" style="width: 16pt; mso-width-source: userset; mso-width-alt: 768;"> <col width="244" style="width: 183pt; mso-width-source: userset; mso-width-alt: 8923;"> <col width="21" style="width: 16pt; mso-width-source: userset; mso-width-alt: 768;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 105, bgcolor: transparent"]Hi Mick,
Is this okay?

Tree 2[/TD]
[TD="class: xl66, width: 21, bgcolor: transparent"][/TD]
[TD="width: 166, bgcolor: transparent"]




Tree 3[/TD]
[TD="class: xl66, width: 21, bgcolor: transparent"][/TD]
[TD="width: 244, bgcolor: transparent"]




Tree 4[/TD]
[TD="class: xl66, width: 21, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]




Result Code[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]00[/TD]
[TD="class: xl65, bgcolor: transparent"]20.00.00[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Underframe[/TD]
[TD="class: xl66, bgcolor: transparent"]01[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]00[/TD]
[TD="class: xl65, bgcolor: transparent"]20.01.00[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Underframe[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Underbody[/TD]
[TD="class: xl66, bgcolor: transparent"]01[/TD]
[TD="class: xl65, bgcolor: transparent"]20.01.01[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Underframe[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Sideframe[/TD]
[TD="class: xl66, bgcolor: transparent"]02[/TD]
[TD="class: xl65, bgcolor: transparent"]20.01.02[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Underframe[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Cross Car Parts[/TD]
[TD="class: xl66, bgcolor: transparent"]03[/TD]
[TD="class: xl65, bgcolor: transparent"]20.01.03[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Underframe[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Rear Structure[/TD]
[TD="class: xl66, bgcolor: transparent"]04[/TD]
[TD="class: xl65, bgcolor: transparent"]20.01.04[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Underframe[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Closures[/TD]
[TD="class: xl66, bgcolor: transparent"]05[/TD]
[TD="class: xl65, bgcolor: transparent"]20.01.05[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Adhesive[/TD]
[TD="class: xl66, bgcolor: transparent"]02[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]00[/TD]
[TD="class: xl65, bgcolor: transparent"]20.02.00[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Adhesive[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Adhesives[/TD]
[TD="class: xl66, bgcolor: transparent"]01[/TD]
[TD="class: xl65, bgcolor: transparent"]20.02.01[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bolt-ons[/TD]
[TD="class: xl66, bgcolor: transparent"]03[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]00[/TD]
[TD="class: xl65, bgcolor: transparent"]20.03.00[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bolt-ons[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Bolt Ons Ancillaries[/TD]
[TD="class: xl66, bgcolor: transparent"]01[/TD]
[TD="class: xl65, bgcolor: transparent"]20.03.01[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bolt-ons[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Bolt Ons Front Structure[/TD]
[TD="class: xl66, bgcolor: transparent"]02[/TD]
[TD="class: xl65, bgcolor: transparent"]20.03.02[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bolt-ons[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Bolt Ons Rear Structure[/TD]
[TD="class: xl66, bgcolor: transparent"]03[/TD]
[TD="class: xl65, bgcolor: transparent"]20.03.03[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bolt-ons[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Bolt Ons Front Bumper Structure[/TD]
[TD="class: xl66, bgcolor: transparent"]04[/TD]
[TD="class: xl65, bgcolor: transparent"]20.03.04[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bolt-ons[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Bolt Ons Rear Bumper Structure[/TD]
[TD="class: xl66, bgcolor: transparent"]05[/TD]
[TD="class: xl65, bgcolor: transparent"]20.03.05[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Front End Module[/TD]
[TD="class: xl66, bgcolor: transparent"]04[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]00[/TD]
[TD="class: xl65, bgcolor: transparent"]20.04.00[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Front End Module[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Front End Module[/TD]
[TD="class: xl66, bgcolor: transparent"]01[/TD]
[TD="class: xl65, bgcolor: transparent"]20.04.01[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Front End Module[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Air-on panels[/TD]
[TD="class: xl66, bgcolor: transparent"]02[/TD]
[TD="class: xl65, bgcolor: transparent"]20.04.02[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]NVH and Heat Protection[/TD]
[TD="class: xl66, bgcolor: transparent"]05[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]00[/TD]
[TD="class: xl65, bgcolor: transparent"]20.05.00[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]NVH and Heat Protection[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]TUB Baffles[/TD]
[TD="class: xl66, bgcolor: transparent"]01[/TD]
[TD="class: xl65, bgcolor: transparent"]20.05.01[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Body Structures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]NVH and Heat Protection[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Damping Pads[/TD]
[TD="class: xl66, bgcolor: transparent"]02[/TD]
[TD="class: xl65, bgcolor: transparent"]20.05.02[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]BIW[/TD]
[TD="class: xl66, bgcolor: transparent"]11[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]00[/TD]
[TD="class: xl65, bgcolor: transparent"]21.00.00[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]BIW[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bridging Structure [/TD]
[TD="class: xl66, bgcolor: transparent"]01[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]00[/TD]
[TD="class: xl65, bgcolor: transparent"]21.01.00[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]BIW[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bridging Structure [/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Rear Quarter[/TD]
[TD="class: xl66, bgcolor: transparent"]01[/TD]
[TD="class: xl65, bgcolor: transparent"]21.01.01[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]BIW[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bridging Structure [/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Trunk Lid Surround [/TD]
[TD="class: xl66, bgcolor: transparent"]02[/TD]
[TD="class: xl65, bgcolor: transparent"]21.01.02[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]BIW[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bridging Structure [/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Bridging Structure Rear End[/TD]
[TD="class: xl66, bgcolor: transparent"]03[/TD]
[TD="class: xl65, bgcolor: transparent"]21.01.03[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]BIW[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bridging Structure [/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Bridging Structure Adhesives[/TD]
[TD="class: xl66, bgcolor: transparent"]04[/TD]
[TD="class: xl65, bgcolor: transparent"]21.01.04[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]BIW[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bodyside[/TD]
[TD="class: xl66, bgcolor: transparent"]02[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]00[/TD]
[TD="class: xl65, bgcolor: transparent"]21.02.00[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]BIW[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bodyside[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Bodyside[/TD]
[TD="class: xl66, bgcolor: transparent"]01[/TD]
[TD="class: xl65, bgcolor: transparent"]21.02.01[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]BIW[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bodyside[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Bodyside Adhesive[/TD]
[TD="class: xl66, bgcolor: transparent"]02[/TD]
[TD="class: xl65, bgcolor: transparent"]21.02.02[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]BIW[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Roof[/TD]
[TD="class: xl66, bgcolor: transparent"]03[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]00[/TD]
[TD="class: xl65, bgcolor: transparent"]21.03.00[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]BIW[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Roof[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Roof Fixed[/TD]
[TD="class: xl66, bgcolor: transparent"]01[/TD]
[TD="class: xl65, bgcolor: transparent"]21.03.01[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]BIW[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Roof[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Roof Adhesive[/TD]
[TD="class: xl66, bgcolor: transparent"]02[/TD]
[TD="class: xl65, bgcolor: transparent"]21.03.02[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]BIW[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Front Fender[/TD]
[TD="class: xl66, bgcolor: transparent"]04[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]00[/TD]
[TD="class: xl65, bgcolor: transparent"]21.04.00[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]BIW[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Front Fender[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Front Fender[/TD]
[TD="class: xl66, bgcolor: transparent"]01[/TD]
[TD="class: xl65, bgcolor: transparent"]21.04.01[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Closures[/TD]
[TD="class: xl66, bgcolor: transparent"]12[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]00[/TD]
[TD="class: xl65, bgcolor: transparent"]22.00.00[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Closures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bonnet Structure[/TD]
[TD="class: xl66, bgcolor: transparent"]01[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]00[/TD]
[TD="class: xl65, bgcolor: transparent"]22.01.00[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Closures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bonnet Structure[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Bonnet [/TD]
[TD="class: xl66, bgcolor: transparent"]01[/TD]
[TD="class: xl65, bgcolor: transparent"]22.01.01[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Closures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bonnet Structure[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Bonnet Hinge[/TD]
[TD="class: xl66, bgcolor: transparent"]02[/TD]
[TD="class: xl65, bgcolor: transparent"]22.01.02[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Closures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bonnet Structure[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Bonnet Support and Struts[/TD]
[TD="class: xl66, bgcolor: transparent"]03[/TD]
[TD="class: xl65, bgcolor: transparent"]22.01.03[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Closures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bonnet Structure[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Bonnet Overslam Bumpers & Wedges[/TD]
[TD="class: xl66, bgcolor: transparent"]04[/TD]
[TD="class: xl65, bgcolor: transparent"]22.01.04[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Closures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Bonnet Structure[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Noise Insulation[/TD]
[TD="class: xl66, bgcolor: transparent"]05[/TD]
[TD="class: xl65, bgcolor: transparent"]22.01.05[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Closures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Front Side Door Structure[/TD]
[TD="class: xl66, bgcolor: transparent"]02[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]00[/TD]
[TD="class: xl65, bgcolor: transparent"]22.02.00[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Closures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Front Side Door Structure[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Front Side Door[/TD]
[TD="class: xl66, bgcolor: transparent"]01[/TD]
[TD="class: xl65, bgcolor: transparent"]22.02.01[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Closures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Front Side Door Structure[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Front Side Door Hinge[/TD]
[TD="class: xl66, bgcolor: transparent"]02[/TD]
[TD="class: xl65, bgcolor: transparent"]22.02.02[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #9BC2E6"]Closures[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #A9D08E"]Front Side Door Structure[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: #FFD966"]Front Side Door Struts[/TD]
[TD="class: xl66, bgcolor: transparent"]03[/TD]
[TD="class: xl65, bgcolor: transparent"]22.02.03[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this:-
Let me know what you want to do with the results and any modifications required.
https://app.box.com/s/ljfvoatymez4jae99unx77tg57dlel72

 

[calumbus53]

WOW! That is fantastic! Thank you Mick!

I have been trying to develop a piece of VBA which I would appreciate if you could give me some advice on? - if you wouldn't mind?

Private Sub cmbTestDesc_Change()
Dim found As Integer
found = 0
Dim matchTest As Variant
matchTest = cmbTestDesc.Value = Application.WorksheetFunction.Index(Sheets("Working_Sheet").Range("B3:B13"), _
Application.WorksheetFunction.Match(cmbTestDesc.Value, Sheets("Working_Sheet").Range("B3:B13"), 0), 1)
If IsError(matchTest) Then
found = 0
MsgBox ("Type Mismatch")
cmbTestDesc.SetFocus
Cancel = True
Exit Sub
Else:
txtTestProc = Application.WorksheetFunction.Index(Sheets("Working_Sheet").Range("C3:C13"), _
Application.WorksheetFunction.Match(cmbTestDesc.Value, Sheets("Working_Sheet").Range("B3:B13"), 0), 1)
txtUTID.Value = Application.WorksheetFunction.Index(Sheets("Working_Sheet").Range("A3:A13"), _
Application.WorksheetFunction.Match(cmbTestDesc.Value, Sheets("Working_Sheet").Range("B3:B13"), 0), 1)
found = 1
End If
End Sub


The code works similar to a vLookup but because I needed a value to the left of the matched value I have used Index and Match. The code works fine when looking up values and populating other cells, but I cannot for the life of me work out how to allow the user to delete, or manually enter a value in the combo box and then press a command "Add" button. This button would then add a line item to the bottom of a worksheet and also send a notification email to the administrator.

Do you know if this is possible?

Many thanks,

Calum

 

[calumbus53]

Try this:-
Let me know what you want to do with the results and any modifications required.
https://app.box.com/s/ljfvoatymez4jae99unx77tg57dlel72

A couple of mods, if possible..?

• I'm unable to rename the sheet or copy the code across to work in my form.
• I need to add in a 4th dependency combo box.
• When deleting one of the dependencies I need the full code box to reset or better still, say for example: (if it was) 11.01.01 and then the final combo box was cleared, (it should say) 11.01.00 Or if one of the dependencies was cleared, it should clear the code.

Thank you for your help mate.

Cal