MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Variable Data Validation


Posted by Zif on January 22, 2001 4:03 AM

Hi

I have a column (Division) which is populated by users selecting a Data Validation entry (or by typing in a valid entry).

In the next column (Department), I would like the data Validation to show only those entries which are valid for the Division entered in the previous column.

For Example:
Division 1 contains Dept1, Dept2, Dept3 and Dept99
Division 2 contains Dept6, Dept7, Dept8, Dept9 and Dept99

If the user specifies Division 1, Data Validation should only allow Dept1, Dept2, Dept3, Dept99.

If the user specifies Division 2, Data Validation should only allow Dept6, Dept7, Dept8, Dept9, Dept99

I'm not sure if this is possible using Data Validation, but I'd like to use this facility so that the users can use the drop down box feature.


Hope someone can help.
Zif


Posted by Aladin Akyurek on January 22, 2001 6:28 AM

Assuming that A1 contains the division value,
B1 the dept value yet to be entered.

Construct a list of all depts, say from C1 on. Enter Dept99 twice if you wish: Dept1, Dept2, Dept3, Dept99, Dept6, Dept7, Dept9, Dept99.

Select B1, go to Data Validation, Select "List", then enter as Source the following formula:

=IF(A1=1,$C$1:$C$4,$C$5:$C$8)

You may name A1 Division via the Name Box and use this name in the above formula instead of A1.

Hope this is what you asked for.

Aladin


Posted by Aladin Akyurek on January 22, 2001 6:29 AM

Assuming that A1 contains the division value,
B1 the dept value yet to be entered.

Construct a list of all depts, say from C1 on. Enter Dept99 twice if you wish: Dept1, Dept2, Dept3, Dept99, Dept6, Dept7, Dept9, Dept99.

Select B1, go to Data Validation, Select "List", then enter as Source the following formula:

=IF(A1=1,$C$1:$C$4,$C$5:$C$8)

You may name A1 Division via the Name Box and use this name in the above formula instead of A1.

Hope this is what you asked for.

Aladin


Posted by Zif on January 22, 2001 8:48 AM

Aladin

That works nicely - but there is one small problem. We have 12 separate divisions and Excel has a limit of 7 nested IFs.

Any suggestions as to how to get around that limitation?

Zif : Hi : I have a column (Division) which is populated by users selecting a Data Validation entry (or by typing in a valid entry). : In the next column (Department), I would like the data Validation to show only those entries which are valid for the Division entered in the previous column. : For Example:

Posted by Aladin Akyurek on January 22, 2001 10:17 AM

Hi Zif,

It's indeed a "small problem": we don't need any IFs.

I'll assume as in the previous post that A1 contains the division value.
In B1 we want the data validated department value.
From C1 on we'll list all departments, starting with dept values associated with Division 1, dept values of Division 2, and so on. We allow repetition, that is, if a certain dept is associated with two or more divisions, we'll enter them again in the appropriate place.
From D1 on we create a series of 1 to 12.
Now we have 1 in D1. We put the following in E1:
$C$1:$C$5 (that is the range where depts of Division reside)
We do the same in E2, that is, we enter the range of dept values associated with Division 2. We iterate this step until we have all 12 ranges in E.

We go to B1, select "List" on Data Validation, and enter as Source:

=INDIRECT(VLOOKUP(A1,$D$1:$E$12,2,0))

This completes the solution (except the messages, I would say).

Aladin

Posted by Zif on January 23, 2001 1:01 AM

Perfect!

Thanks for your help.

Zif

: Aladin : That works nicely - but there is one small problem. We have 12 separate divisions and Excel has a limit of 7 nested IFs. : Any suggestions as to how to get around that limitation? : Zif Hi Zif, It's indeed a "small problem": we don't need any IFs. I'll assume as in the previous post that A1 contains the division value. In B1 we want the data validated department value. From C1 on we'll list all departments, starting with dept values associated with Division 1, dept values of Division 2, and so on. We allow repetition, that is, if a certain dept is associated with two or more divisions, we'll enter them again in the appropriate place. From D1 on we create a series of 1 to 12. Now we have 1 in D1. We put the following in E1: $C$1:$C$5 (that is the range where depts of Division reside) We do the same in E2, that is, we enter the range of dept values associated with Division 2. We iterate this step until we have all 12 ranges in E. We go to B1, select "List" on Data Validation, and enter as Source: =INDIRECT(VLOOKUP(A1,$D$1:$E$12,2,0)) This completes the solution (except the messages, I would say). Aladin