Limit selection from drop-down data validation list

[Magnetar]

Hi there folks!

I have a very simple request (at least I hope it’s going to be!).
I would like to limit the selection from a long drop-down list so to speak on selection of specific data from another list.

To explain further: I have set up the following three ‘Data -> Validation’ lists within a ‘LookUpRef’ worksheet, as follows:


DEPT TEAM SelectDept
A QCA1 A
A QCA2 B
A QCA3 C
A QCA4 D
B QCA5
B QCA6
B QCA7
B QCA8
C QCA9
C QCA10
C QCA11
C QCA12
D QCA13
D QCA14
D QCA15
D QCA16


On another sheet, I enter the ‘Department’ in one column from the drop-down ‘SelectDept’ list, and the ‘Teams’ into another column from the ‘TEAM’ drop-down list.

That is,

Department Teams
A QCA1
A QCA3
D QCA14

- etc,…

On selecting and entering a ‘Department’, - say ‘A’ for example, from the ‘DEPT’ list I would then like to see only a limited selection: ‘QCA1’, ‘QCA2’, ‘QCA3’ and ‘QCA4’ within the ‘TEAM’ list.

Can this be done very simply within code? I would like to activate/run this functionality each time a ‘Department’ is selected and entered. Or is there a much simpler way, involving the standard Excel features?

Many thanks in advance for assistance with this.

Kind regards

Magnetar

 

[GlennUK]

Name the cells containing
‘QCA1’, ‘QCA2’, ‘QCA3’ and ‘QCA4’
as a defined name of A
and the cells containing
'QCA5', 'QCA6', 'QCA7' and 'QCA8'
as a defined name of B
and so on for each department.

Then, for the data validation for the Team cell, use
=INDIRECT(deptchoice)
as the source list.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Magnetar]

Glenn, hi.

ThanX for your response.
Sorry not very clear with your solution. Also when entering either "=INDIRECT(DEPT)" or "=INDIRECT(TEAM)", I obtain the error message:
"The source currently evaluates to an error".

Also there doesn't seem to be a reference for the actual 'TEAM' list itself as such within your solution, which is where I actually need select my values for entry from.

Is there not a simpler way of limiting the 'TEAM' list (from my previous example)?

I would prefer to do this within code if possible.

Kind regards

Magnetar

 

[Magnetar]

Hi Glenn, & to anyone else who can help me, please.

What I would really like is the equivalent of the following scenario (using data from my previous example):


Sub LimitTheList()

Range("F1").Select
'i.e. entering data in this column, selected from the 'TEAM' drop-down list.

With Range("SelectDept") 'i.e. look at data within 'SelectDept' drop-down list

'Look at the range 'DEPT'
'Compare 'SelectDept' entered with values from 'DEPT

'See which teams within range 'TEAM' pertain to selected 'DEPT'

'Limit drop-down list to those teams

End With

End Sub

Would be really grateful with some help with this.

Many thanX

Magnetar

 

[xlbo]

What Glen has offered should work - have a look here for more info

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[sawedoff]

Glenn's solution works.
The range names have to match the data that will be entered into the Dept column. Thus if the user will enter "A" or "B" as the department, the range needs to be named "A" or "B". Naming them "Dept" or "Team" will only work if the user inserts those words into the column.

Sawedoff

 

[Magnetar]

To Glenn & Geoff

Many, many thanX for your help guys.

Geoff, that page at:

http://www.contextures.com/

was just what I needed. Did explain things more clearly.
Thank you for your initial suggestion, Glenn, - much appreciated!

Kind regards

Magnetar

===============================

 

[Magnetar]

...and many thanX Sawedoff, for your brief, but simple explanation (finally got there!).
Ta, muchly!

Kind regards

Magnetar

===============================