Dependent validation list

[rain4u]

Hi all
I give up. After trying to figure it our and learn it myself for the last day and a half I think I need someones help who is more experienced.

I'm working to create some macros to help me at work. I'm trying to set up a validation list on two columns that would be linked. I'm not trying to go any further than than having two columns linked. Two will be sufficient.

To explain it in a short way it would work very similarly as autofilter. Let me illustrate.
My source data for the validation lists is on sheet called "properties suppliers".
My data looks as below. I have used dummy data (country names) to visually be more understandable.

Column C has a dynamic named range "=SourceList_DYNAMIC_Suppliers". It covers everything from C2 downwards to the last cell used (in column C that is).
Example1
Col B --------------------- Col C ----------------------
internal ------------- Afghanistan - internal -------------
xdock --------------- Afghanistan - xdock -------------
internal ------------- Barbados - internal -------------
internal ------------- Belgium - internal -------------
xdock --------------- Belgium - xdock -------------
internal ------------- China - internal -------------
xdock --------------- China - xdock -------------
internal ------------- Canada - internal -------------
xdock --------------- Canada - xdock -------------
internal ------------- Colombia - internal -------------
xdock --------------- Colombia - xdock -------------
xdock --------------- Denmark - xdock -------------
internal ------------- Egypt - internal -------------
xdock --------------- Egypt - xdock -------------
internal ------------- Finland - internal -------------
internal ------------- France - internal -------------
xdock --------------- France - xdock -------------
internal ------------- Germany - internal -------------
internal ------------- Gibraltar - internal -------------
xdock --------------- Gibraltar - xdock -------------
internal ------------- Haiti - internal -------------
xdock --------------- Haiti - xdock -------------
internal ------------- Honduras - internal -------------
xdock --------------- Honduras - xdock -------------
internal ------------- Iceland - internal -------------
xdock --------------- Iceland - xdock -------------
internal ------------- Iraq - internal -------------
xdock --------------- Iraq - xdock -------------
internal ------------- Ireland - internal -------------
xdock --------------- Ireland - xdock -------------
internal ------------- Latvia - internal -------------
xdock --------------- Latvia - xdock -------------
internal ------------- Luxembourg - internal -------------
xdock --------------- Luxembourg - xdock -------------
internal ------------- Madagascar - internal -------------
internal ------------- New Zealand - internal -------------
xdock --------------- New Zealand - xdock -------------
xdock --------------- Norway - xdock -------------
internal ------------- Panama - internal -------------
xdock --------------- Panama - xdock -------------
internal ------------- Paraguay - internal -------------
internal ------------- Poland - internal -------------
xdock --------------- Poland - xdock -------------
internal ------------- Portugal - internal -------------
xdock --------------- Portugal - xdock -------------
internal ------------- Romania - internal -------------
xdock --------------- Romania - xdock -------------
internal ------------- Russia - internal -------------
xdock --------------- Russia - xdock -------------
internal ------------- Samoa - internal -------------
internal ------------- San Marino - internal -------------
xdock --------------- San Marino - xdock -------------
internal ------------- Senegal - internal -------------
internal ------------- Seychelles - internal -------------
xdock --------------- Seychelles - xdock -------------
internal ------------- Tanzania - internal -------------
xdock --------------- Tanzania - xdock -------------
internal ------------- Togo - internal -------------
xdock --------------- Togo - xdock -------------
internal ------------- Tonga - internal -------------
xdock --------------- Tonga - xdock -------------
internal ------------- Congo - internal -------------
xdock --------------- Congo - xdock -------------
internal ------------- Yemen - internal -------------
xdock --------------- Yemen - xdock -------------



On another sheet called "properties email" I have values in cell G4 and G5.internal and xdock (respectively)
Cell G4 and G5 has a dynamic named range (=SourceList_STATIC_internal_xdock). It's a static named range, that covers only these two cells.
--------------------- Col G
Row 4----------- internal
Row 5----------- xdock


At the moment I have a two validation lists set up on a sheet called "Cant 101" in column E and column F.

Column E. Its source is "=SourceList_STATIC_internal_xdock" so if I would click in any cell in col E it would bring me up the list of:
internal
xdock

Column F. Its source is "=SourceList_DYNAMIC_Suppliers" so if I would click in any cell it would bring me up the whole list the whole 65 lines (as in example1).

What I would like to achieve is that if would go and click lets say on cell E2, choose internal, then click F2, instead bringing up the whole 65 lines, it would only bring up the internal ones.


Continues on the next post.

[rain4u]

Example2:

Col B --------------------- Col C ----------------------
internal ------------- Afghanistan - internal -------------
internal ------------- Barbados - internal -------------
internal ------------- Belgium - internal -------------
internal ------------- China - internal -------------
internal ------------- Canada - internal -------------
internal ------------- Colombia - internal -------------
internal ------------- Egypt - internal -------------
internal ------------- Finland - internal -------------
internal ------------- France - internal -------------
internal ------------- Germany - internal -------------
internal ------------- Gibraltar - internal -------------
internal ------------- Haiti - internal -------------
internal ------------- Honduras - internal -------------
internal ------------- Iceland - internal -------------
internal ------------- Iraq - internal -------------
internal ------------- Ireland - internal -------------
internal ------------- Latvia - internal -------------
internal ------------- Luxembourg - internal -------------
internal ------------- Madagascar - internal -------------
internal ------------- New Zealand - internal -------------
internal ------------- Panama - internal -------------
internal ------------- Paraguay - internal -------------
internal ------------- Poland - internal -------------
internal ------------- Portugal - internal -------------
internal ------------- Romania - internal -------------
internal ------------- Russia - internal -------------
internal ------------- Samoa - internal -------------
internal ------------- San Marino - internal -------------
internal ------------- Senegal - internal -------------
internal ------------- Seychelles - internal -------------
internal ------------- Tanzania - internal -------------
internal ------------- Togo - internal -------------
internal ------------- Tonga - internal -------------
internal ------------- Congo - internal -------------
internal ------------- Yemen - internal -------------


Now I'm not quite sure this could be achieved. I have searched over the Internet for some solutions. I have seen some similar stuff but I do not know how to implement it to my own project. One of the problems is that I can not separate the the internal and xdock into to a separate columns as there are some overlappings with other information on the spreadsheet. Or at least I can not visualize it.

Can someone help me me to see if this sort of dependent lists could be created? If its impossible then I will just give up.
I have attached a spreadsheet for a better understanding. It does include some macros. Main macro is called "refresh" and is in module 1.

Any help is appreciated.
Cheers
Rain

[watersev]

hi, rain, option to make it with additional hidden sheet

[snb]

Download this file and see how easy...

If you use VBA you won't need named ranges.

[rain4u]

Hi snb
Thanks for your input. The xls file that you recommended contains some macros that are bit over my current knowledge level for me to understand. But no probs.I spent another hour trying to learn more about dependent validation lists and I stumbled up on this: http://www.contextures.com/xldataval13.html#Top
I managed to use this tutorial and I achieved the result that I was after. Thank you for your time.


Cheers

[rain4u]

hi, rain, option to make it with additional hidden sheet

Unbelievable. I only now noticed your post. Oh man. Cursing myself..... Well I can see this is a macro based solution. I will try to learn from it. I can sort of understand the concept. Amazing stuff. I wish I could achieve this kind of stuff myself. There is a very long way for me to go.


Thank you!