Data Validation - Conditional List Boxes

Posted by Jeremy on January 05, 2001 12:47 PM

This is an addendum to my original post (located below).

The list box is partially working. The problem is that the Department Numbers are formatted as follows:
1 - Finance
2 - Sales

For some reason, the formula I was given (from this site) doesn't recognize text....only numbers. Is there a way around this?

Thanks,
Jeremy

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.

Posted by Tim Francis-Wright on January 05, 2001 8:10 PM

I think that you just need to modify my previous solution a bit.
Add a row underneath the row with '1 finance' etc.
that just has 1 2 3 etc. You'll then need to modify
the data validation setting for the second of the input cells.
[>> indicates a change]

The departments are in cells A1:AP1
>> In cells A2:AP2 have the number associated
>> with the department.

The allowable units for each department are in
cells >>4:21<&LT; of each column. [Change 21 to a higher
number if a department could have over 18 units,
or to a smaller number, if no department could
have that many units.]
In cell >>A3<&LT;, have the formula:-
>>
=COUNT(A4:A21)
&LT;&LT;
Copy this over to cells >> B3:AP3.

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(A4,0,HLOOKUP(A25,A1:AP2,2,FALSE)-1,OFFSET(A3,0,HLOOKUP(A25,A1:AP2,2,FALSE)-1),1)
&LT;&LT;
OFFSET returns a list of values from a range:
A4 is the starting cell.
0 says to go 0 rows down
HLOOKUP(A25,A1:AP2,2,FALSE) says to find the number corresponding to the department and go that many columns minus 1 to the right
OFFSET(A3,0,HLOOKUP(A25,A1:AP2,2,FALSE)-1) finds the appropriate COUNT result: this is the number of columns to return
1 says to return a range with 1 row

Good luck!