Conditional Validation List

[GDS]

I have a somewhat large dataset that has two types of information: agreement number and program code. One agreement number can have several program codes.

The list looks something like this:

RB0110009 BHT
RB0110009 TTG
RB0110010 BHT
RB0110010 CCC
RB0110010 TTG
RB0110010 WXV
RB0110011 TTG

I want to set up a conditional validation list so that when the user selects RB0110010, then on the next column, they are limited to BHT, CCC and TTG as their choices of program.

This doesn't seem to be possible with the simple INDIRECT command that is used on most of the online examples of conditional validations so any help would be greatly appreciated.

Thanks in advance.

 

[VoG]

Does this help http://www.contextures.com/xlDataVal02.html

 

[GDS]

That is actually the site that first got me thinking this was possible, but it seems to suggest that you need to define all your lists ahead of time meaning that I would have to make a list of programs for each agreement (supporting lists). The problem is that I have over 700 agreements and the list of programs within each agreement changes meaning I would have to redfine my program lists all the time.

What I want to do is filter the choices based on the first validation.