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:

	Parent Selection	1 Child Selection	2 Child Selection			Parent		1st Child		2 Child
10.02.02	a	a2.1	aa2.1		10	a
10.03.03	a	a3.1	aa3.1			a	01	a1.1
11.02.02	b	b2.2	bb2.1			a		a1.2	01	aa1.1
						a		a1.3		aa1.2
						a		a1.4		aa1.3
						a		a1.5		aa1.4
						a
						a	02	a2.1
						a		a2.2	02	aa2.1
						a
						a	03	a3.1
						a		a3.2	03	aa3.1
						a		a3.3		aa3.2
						a
						a	04	a4.1
						a		a4.2	04	aa4.1
					11	b
						b	01	b1.1
						b		b1.2	01	bb1.1
						b
						b	02	b2.1
						b		b2.2	02	bb2.1
						b		b2.3		bb2.2
						b
						b	03	b3.1
						b		b3.2	03	bb3.1

<tbody>
</tbody><colgroup><col><col><col span="2"><col><col><col><col><col><col><col></colgroup>

 

[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".

Results	Val1	Val2	Val3
10.02.02	a	a2.1	aa2.1
10.03.03	a	a3.1	aa3.1
11.02.02	b	b2.2	bb2.1

<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> </tbody>

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]

Hi Mick, Is this okay? Tree 2		Tree 3		Tree 4		Result Code
Body Structures	10		00		00	20.00.00
Body Structures		Underframe	01		00	20.01.00
Body Structures		Underframe		Underbody	01	20.01.01
Body Structures		Underframe		Sideframe	02	20.01.02
Body Structures		Underframe		Cross Car Parts	03	20.01.03
Body Structures		Underframe		Rear Structure	04	20.01.04
Body Structures		Underframe		Closures	05	20.01.05
Body Structures		Adhesive	02		00	20.02.00
Body Structures		Adhesive		Adhesives	01	20.02.01
Body Structures		Bolt-ons	03		00	20.03.00
Body Structures		Bolt-ons		Bolt Ons Ancillaries	01	20.03.01
Body Structures		Bolt-ons		Bolt Ons Front Structure	02	20.03.02
Body Structures		Bolt-ons		Bolt Ons Rear Structure	03	20.03.03
Body Structures		Bolt-ons		Bolt Ons Front Bumper Structure	04	20.03.04
Body Structures		Bolt-ons		Bolt Ons Rear Bumper Structure	05	20.03.05
Body Structures		Front End Module	04		00	20.04.00
Body Structures		Front End Module		Front End Module	01	20.04.01
Body Structures		Front End Module		Air-on panels	02	20.04.02
Body Structures		NVH and Heat Protection	05		00	20.05.00
Body Structures		NVH and Heat Protection		TUB Baffles	01	20.05.01
Body Structures		NVH and Heat Protection		Damping Pads	02	20.05.02
BIW	11		00		00	21.00.00
BIW		Bridging Structure	01		00	21.01.00
BIW		Bridging Structure		Rear Quarter	01	21.01.01
BIW		Bridging Structure		Trunk Lid Surround	02	21.01.02
BIW		Bridging Structure		Bridging Structure Rear End	03	21.01.03
BIW		Bridging Structure		Bridging Structure Adhesives	04	21.01.04
BIW		Bodyside	02		00	21.02.00
BIW		Bodyside		Bodyside	01	21.02.01
BIW		Bodyside		Bodyside Adhesive	02	21.02.02
BIW		Roof	03		00	21.03.00
BIW		Roof		Roof Fixed	01	21.03.01
BIW		Roof		Roof Adhesive	02	21.03.02
BIW		Front Fender	04		00	21.04.00
BIW		Front Fender		Front Fender	01	21.04.01
Closures	12		00		00	22.00.00
Closures		Bonnet Structure	01		00	22.01.00
Closures		Bonnet Structure		Bonnet	01	22.01.01
Closures		Bonnet Structure		Bonnet Hinge	02	22.01.02
Closures		Bonnet Structure		Bonnet Support and Struts	03	22.01.03
Closures		Bonnet Structure		Bonnet Overslam Bumpers & Wedges	04	22.01.04
Closures		Bonnet Structure		Noise Insulation	05	22.01.05
Closures		Front Side Door Structure	02		00	22.02.00
Closures		Front Side Door Structure		Front Side Door	01	22.02.01
Closures		Front Side Door Structure		Front Side Door Hinge	02	22.02.02
Closures		Front Side Door Structure		Front Side Door Struts	03	22.02.03

<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> </tbody>

 

[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