# Drop down List based on criteria

#### stugi

##### New Member
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.

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### GlennUK

##### Well-known Member
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

##### New Member
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

##### Well-known Member

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

#### stugi

##### New Member
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

##### Well-known Member
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

##### New Member
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

Replies
20
Views
926
Replies
4
Views
2K
Replies
5
Views
131
Replies
3
Views
105
Replies
4
Views
411

1,190,818
Messages
5,983,060
Members
439,818
Latest member
schizoid231

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back