Hurry - Data Validation Conditional List Boxes!!

Posted by Jeremy on January 04, 2001 8:51 AM

Here's the data:
I have a list of department numbers (1-44) and within each department number are numerous unit numbers. For instance, Department 1 is made up of unit numbers 70 - 300 (the unit numbers are listed in 10's; i.e., 70, 90, 120, etc).

Here's the question:
I have already created a list box using Data Validation that lists all the department numbers. Now, I the Unit Number list box to only list the unit numbers that correspond to a particular department. For example, if the user selects department 1, then I want the unit number list box to only list the units that are associated with Department 1. Currently, my list box contains EVERY unit in our company.

Any help would be great!

Thanks,
Jeremy
Birmingham, AL

Posted by Tim Francis-Wright on January 04, 2001 9:24 AM

I've assumed the following (you will doubtless
need to change things to fit your workbook):

The departments are in cells A1:AP1
The allowable units for each department are in
cells 3:20 of each column. [Change 20 to a higher
number if a department could have oevr 18 units,
or to a smaller number, if no department could
have that many units.]
In cell A2, have the formula:-
=COUNT(A3:A20)
Copy this over to cells B2:AP2.

The data validation for the department input is:
List/ A1:AP1

if the department input is in cell A25, then
the data validation for the unit input is:
List/ =OFFSET(A3,0,A25-1,OFFSET(A2,0,A25-1),1)

OFFSET returns a list of values from a range:
A3 is the starting cell.
0 says to go 0 rows down
A25-1 says to go A25-1 columns to the right
OFFSET(A2,0,A25-1) finds the appropriate COUNT result: this is the number of columns to return
1 says to return a range with 1 row

I tested this on a toy model, and it worked fine.
Good luck!

Posted by Jeremy on January 04, 2001 9:51 AM

Man, that worked out perfectly! Thank you!!!

Jeremy

Posted by Jeremy on January 04, 2001 9:52 AM

Thank You!!

Thank you!!!