• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Advanced Data Validation : 3 columns

C

cyrilz

New Member
Hello all,


I've used the tip shown in a great Chandoo post here


I wanted 3 columns instead of 2.


I've managed to create the data validation formula for the 3rd column, but I've noticed that the 2nd one contains multiple identical lines.


How can I remove all these lines from this second list ?


Example :

[pre]
Code: 
A     B      C
A1    B1     C1
A1    B1     C2
A1    B2     C1
[/pre]

I will Have B1 two times and B2 one time in the second list if I select A1


Thanks in advance


Cyril.
 
The only way I can think of it possible working with 3 dependent lists is to use Named Ranges, as described here:

http://contextures.com/xlDataVal13.html
 
First of all many thanks to you all,


> Narayan, Since these lists are to be used into a long list of use cases, it seems to me difficult to adapt helper columns to each row, but who knows ?

The only solution I found is to add an helper refernce table containing only the first 2 columns, with only relevant unique data.


I can not use any macro though.


> Luke M : I look at it, but it quite similar to Chandoo's post I mentionned. I need 3 columns not 2.


If anybody else has an idea... :)
 
Hi Luke ,


I think there may be a problem ; the example given in the Contextures blog does not have duplicates in the second column.


Narayan
 
Narayan,


Correct, it follows a completely different methodology. Requires you to setup a named range for each possible choice. So, you would need to create a b1 list and a b2 list. Quick outline, using first row as List Names

[pre]
Code: 
A1	B1	B2
B1	C1	C1
B2	C2
[/pre]
So, if I select A1 from first dropdown, the INDIRECT function in 2nd dropdown goes to A1 list and shows B1 and B2. Then, if you pick B1 in 2nd dropdown, the INDIRECT function in 3rd dropdown shows C1 and C2.


EDIT: Oops, my apologies. I referenced the wrong article. This is the one that uses the INDIRECT method:

http://contextures.com/xlDataVal02.html
 
Luke,


This is much more interesting for me. I like the idea of grouping horizontally the item for A and vertically the item for B.


But 2 remarks :


1/ I guess B1 and B2 under A1 are useless

2/ If I have A2 and A3 to fill, I would put them on new rows.


Thanks agains.


cyril
 
Cyril,


I don't think my example was clear enough, due to being too small. It's not so much that they are grouped horizontally, it's that there's a vertical list for EVERY choice. Using a larger example:

[pre]
Code: 
Drop1	A1	A2	A3	B1	B2	B3	B4
A1	B1	B1	B2	C1	C1	C1	C4
A2	B2	B2	B4	C2		C3
A3	B3	B4
B4
[/pre]
This is the Data setup. What I would use this for is if Dropdown1 uses the choices listed under Drop1, namely: A1, A2, and A3. Whatever is chosen from there then determine what the next list to look at in dropdown2 will be. If A3 was chosen, then the choices under A3 are B2 and B4. Then, the third dropdown looks at either the B2 list (C1 only) or the B4 list (C4 only)

As I said, can lead to a lot of setup, but the value of effort may depend on how many choices you have.


Does that help?
 
Hi Cyril ,


Can you check this out ?


http://www.get-digital-help.com/2010/07/17/create-dependent-drop-down-lists-containing-unique-distinct-values-in-excel/


Narayan
 
>Luke M : MAny thanks for your help. Unfortunately my end-user didn't understood "Named Ranges" (too complex for him). So I changed for the following :

[pre]
Code: 
C   D      E       F       G       H  ...
-------------------------------------------
1   A1     A1B1    A1B2    A1B3
1          A1B1C1  A1B2C1  A1B3C1
1          A1B1C2  ...
1          ...
2   A2     A2B1    ...
2   ...

Plus some references to avoid spaces between A1/A2 in the Validation List for A items, like this (see chandoo indexed lists) :

[pre][code]M      N         O          P          ...     R           S          T           U
-------------------------------------          -------------------------------------
IndexA  ListValA  StartRow  # B Items          TestValidA  TestValidB  TestValidC  IdxItemA
1      A1        3          3                 A2     [v]  B2     [v]  C1     [v] 8
2      A2        8          1
...

Then I declare 3 Data validation lists as follow :


For List A (quite obvious)

[pre][code]=ListValidA
[/pre]
For List B - I search for the Item B in the line (E1:L1) starting with the Item A selected before (line of this item is in U3) :

=OFFSET( $E$1:$L$1; U3-1;0;1; OFFSET( $P$2; MATCH(R3;$N$3:$N$12;0);0))[/code][/pre]
For ListC - I select the C items between starting rows of item A selected and the next one (index). V3 contains IndexItemB (not enough space)

=OFFSET($D$1;U3;V3;COUNT(OFFSET($D$1;U3;MATCH(S$3;OFFSET(E1:L1;U3-1;0);0);
OFFSET($O$2;MATCH(R3;ListValidA;0)+1;0)-U3-1;1)))[/code][/pre]
Hope this is clear enough.


Cyril
 
Hello again,


I've encountered another problem, since I wanted several sheets to have the validation lists mentionned above.


Data validation lists don't accept references to other sheets. I've used Named ranges (3 of them, something bearable for my end-users, they won't even know about them :)


So you will have to change $D$1, $O$2, $P$2 and $E$1:$L$1 to named ranges, in the above formulae


Regards


Cyril.
 
Hi, Cyril!


Sorry for arriving at dessert time, and I apologize if I misunderstood the main point.


Maybe you want to give a look at this post: http://chandoo.org/forums/topic/data-validation-list-automatically-shows-the-first-item-in-the-list


There is a link to an uploaded file that perhaps may be helpful. I wrote it a few days ago, and even it handles only two levels of chained lists the technique doesn't limit the number of dependencies.


Hope it aids you. Just advise if any doubt.


Regards!
 
Hi SirJB7,


I've seen your post which is nice as always, but as I mentioned above, I cannot use macros (users are just way too afraid of them) just because of the "alert" popup asking to activate content.


They think that there is a virus or some threat so they always close the document.


Regards.


Cyril.
 
Hi, Cyril!


Thanks for your nice comments.

In this case, you can't use macros or you don't want to frighten your users with the alert pop-up? If it's the first case, sorry but I don't think there's a workaround. But it's the second case, you have two alternatives:

a) digitally sign your VBA projects, and adjust Excel trust center and macro parameters

b) create a trusted folder, move your macro enabled projects there, adjust Excel as previously, and don't mention it to your users... so as to not incite them to do the same and move workbooks with macros there


Regards!
 
Thanks SirJB7 but I need to bypass my IT men in both case :)

Hopefully, I can create trusted folders for my own laptop.


Regards.
 
You must log in or register to reply here.
Back
Top