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]

 

[calumbus53]

Here is an image of my form and data in the background. You can see it's combo4 I'm struggling with.

 

[MickG]

You need to send the File not an Image !!!
Have you tried "Box"

Try this:-
See sheet2 for new code on new data.
https://app.box.com/s/3z24nvqri6tbybdffxnfp5v1ifjr980f

 

[calumbus53]

THAT WORKS GREAT!!! THANK YOU SO MUCH!!!

Problem solved.

Cheers

Cal

 

[MickG]

Great news !!
You're welcome

 

[calumbus53]

Hi Mick,

Me again! - thank you for your help.

Do you know if it would be possible to select multiple items within the comboBox's "Commodity Group" / "Commodity"? and then If clicked again, deselect...

cheers.

 

[MickG]

What combobox is the "Commodity Group" / "Commodity" in, I don't see those values anywhere !!!!