# Produce List

#### Herbiec09

##### Active Member
Hi All,

I have a spreadsheet which has student names (column a) and their exam scores in column b. On another tab, I would like to list all the students that score above a certain grade i.e. where the value in column b is above a certain value. I would like to vary this value using a drop down menu. i.e. i select the threshhold value from the drop down and excel produces a list (formula driven).

Not sure how to quite achieve the right formula, but am sure it has combinations of things like index, count etc.

Hope this makes sense.

Can anyone assist

Thanks

Herbert

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi Herbert,

Does this work for you?....

Sample Data1.....

Excel Workbook
ABCDEF
2Bill82**Bill*
3Ben78**Ben*
4Joe78**Joe*
5Bob96**Bob*
6Jim99**Jim*
7Rob73****
8******
Sheet12

Sample Data2.....

Excel Workbook
ABCDEF
2Bill70**Ben*
3Ben90**Bob*
4Joe83**Rob*
5Bob88****
6Jim73****
7Rob89****
8******
Sheet12

The formula in E2 needs to be entered using ctrl shift enter NOT just enter, it can then be copied down as far as you require.
D1 represents the cell with your drop down list.

I hope that helps.

Ak

Last edited:
Hi Herbert,

Does this work for you?....

Sample Data1.....

Excel Workbook
ABCDEF
2Bill82**Bill*
3Ben78**Ben*
4Joe78**Joe*
5Bob96**Bob*
6Jim99**Jim*
7Rob73****
8******
Sheet12

Sample Data2.....

Excel Workbook
ABCDEF
2Bill70**Ben*
3Ben90**Bob*
4Joe83**Rob*
5Bob88****
6Jim73****
7Rob89****
8******
Sheet12

The formula in E2 needs to be entered using ctrl shift enter NOT just enter, it can then be copied down as far as you require.
D1 represents the cell with your drop down list.

I hope that helps.

Ak

Right on the money Akshwani, works like a charm, much appreciated.

H

Hi Herbert,

I'm pleased it worked for you and thanks for the feedback.

Ak

Right on the money Akshwani, works like a charm, much appreciated.

H

Hi Akshwani,

It's me again with a slight variation of the problem above. How about if I had 85 in cell C1 and I wanted to say bring up all names where the number is between 75 & 85, i.e. between D1 & C1.

Thanks

herbz

Right on the money Akshwani, works like a charm, much appreciated.

H

Hi Akshwani,

It's me again with a slight variation of the problem above. How about if I had 85 in cell C1 and I wanted to say bring up all names where the number is between 75 & 85, i.e. between D1 & C1.

Thanks

herbz

Hi herbz.

Perhaps something like this...

Excel Workbook
ABCDEF
2Bill70Bill
3Ben90Joe
4Joe83Jim
5Bob88
6Jim73
7Rob89
8
Sheet12

The formula in E2 needs to be entered using ctrl shift enter NOT just enter, it can then be copied down as far as you require.

I hope that helps.

Ak

Replies
5
Views
136
Replies
9
Views
349
Replies
12
Views
221
Replies
9
Views
145
Replies
6
Views
436

1,203,521
Messages
6,055,883
Members
444,830
Latest member

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