dependant lists using dynamic data

[Yofnik]

Hello All,
I have seen this question asked a number of times, but none of the
suggested solutions fit my needs. Any other suggestions are greatly
appreciated.

I have a Worksheet that contains data that is exported from another
application. The data has the following format:

COL 1 COL 2
---------- ----------
A 1
A 2
A 3
A 4
B 5
B 6
B 7
C 8
C 9
C 10
C 11

On another worksheet, I need to use data validation to force users to
select these values from drop down lists. Here is the catch, on sheet
2 if the user selects A front the first column, I need to show only
1-4 in the second column drop down list. If they select B, then I need
to show 5-7, and so on.

I have seen suggestions on how to make this work, but they all involve
creating separate named ranges for each of the options in column 1.
This is not feasible as there is far too much data and it might change
often.

Is there a way to do this some other way - perhaps using array or
other types of formulas? If VBA is absolutely required, can someone
provides some pointers to get me started.

Thanks,
Yofnik

 

[Debra Dalgleish]

There's a sample file here that creates dependent data validation lists
from a sorted master list:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0036 - Dependent Cell List Offset'

 

[Yofnik]

That helped. Thanks.