Drop down List based on criteria

[stugi]

Hi,

I'm trying to design a payment request form that provides drop down lists of authorised signatories based on the value of the payment. There will be two signatory drop down lists.
The first list will be based on the value of the payment, the 2nd list will be based on the first list.

The authorisation ranges are as follows

0 - 10,000 1 Person from List C
10,000 - 50,000 (Mr A or Mr B + 1 person from List C) or 1 Person from List B
50,000 - 250,000 (Two from List B or 1 from List A)
250,000 - 500,000 Mr D and either (Mr E or Mr F or I list A)
500,000 + Mr D or Mr E or Mr F + I List A

List C - Mr A, Mr B, Miss C, Mr AB
List B - Mr D, Mr E, Mr F, Mr G, Mr H, Mr I
List A - Mr Z, Mr X, Mr Y, Mr T

so for example if I was requesting a payment of 45,000 then

List 1 = Mr A, Mr B, Miss C, Mr AB, Mr D, Mr E, Mr F, Mr G, Mr H, Mr I
List 2 = Mr B (if Mr A was selected List 1), Miss C, Mr AB OR Mr A (if Mr B was select in List 1), Miss C, Mr AB OR the list would be blank if List 1 selected Mr D, Mr E, Mr F, Mr G, Mr H, Mr I

I presume I will need some sort of indexing and various list combinations but I'm not sure how to string it together. Any help would be appreciated.

 

[GlennUK]

I think you ought to look at dependent drop-downs as Debra Dalgleish describes them first:
http://www.contextures.com/xlDataVal02.html

And also think about your logic as you describe in your example ... it's not what should happen. A payment of 45,000 would have:
List 1 = Mr A, Mr B, Mr D, Mr E, Mr F, Mr G, Mr H, Mr I
and if Mr A or Mr B were chosen for the first drop-down then
List 2 = Mr A, Mr B, Mr C, Mr AB ( excluding whoever was already chosen in first drop-down )
and if someone other than Mr A or Mr B was chosen for the first drop-down, then that choice must have been from List B, and the second drop-down should be empty.

 

[stugi]

see its even confused me and its my example.

You are right it Should be Mr A or Mr B and List B only in the List 1 field, my apologies.

 

[GlennUK]

Glad that's sorted out.

Have you had a look at Debra's wonderful site yet?

 

[stugi]

working on it now thanks.

I've used some fomula to assign values to each of the monetary ranges (0 - 10000 = 1, 10000.01 - 50000.00 = 2), I presume I substiture these for the initial Fruit/Vegetable category in the example

 

[GlennUK]

I haven't thought about it much yet .. am quite busy today, and only have 30 minutes before going home. If you get stuck post back, but I doubt I'll be able to look at it until tomorrow.

 

[stugi]

is that the time, woohoo.

I'll have a play but not until Friday now as I'm on a First Aid course tomorrow, so i'll let you know.

thanks