Produce List

[Herbiec09]

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

 

[Akashwani]

Hi Herbert,

Does this work for you?....

Sample Data1.....

Excel Workbook
	A	B	C	D	E	F
1	Name	Task Done	*	75	Name	*
2	Bill	82	*	*	Bill	*
3	Ben	78	*	*	Ben	*
4	Joe	78	*	*	Joe	*
5	Bob	96	*	*	Bob	*
6	Jim	99	*	*	Jim	*
7	Rob	73	*	*	*	*
8	*	*	*	*	*	*
Sheet12

Sample Data2.....

Excel Workbook
	A	B	C	D	E	F
1	Name	Task Done	*	85	Name	*
2	Bill	70	*	*	Ben	*
3	Ben	90	*	*	Bob	*
4	Joe	83	*	*	Rob	*
5	Bob	88	*	*	*	*
6	Jim	73	*	*	*	*
7	Rob	89	*	*	*	*
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

 

[Herbiec09]

Hi Herbert,

Does this work for you?....

Sample Data1.....

Excel Workbook
	A	B	C	D	E	F
1	Name	Task Done	*	75	Name	*
2	Bill	82	*	*	Bill	*
3	Ben	78	*	*	Ben	*
4	Joe	78	*	*	Joe	*
5	Bob	96	*	*	Bob	*
6	Jim	99	*	*	Jim	*
7	Rob	73	*	*	*	*
8	*	*	*	*	*	*
Sheet12

Sample Data2.....

Excel Workbook
	A	B	C	D	E	F
1	Name	Task Done	*	85	Name	*
2	Bill	70	*	*	Ben	*
3	Ben	90	*	*	Bob	*
4	Joe	83	*	*	Rob	*
5	Bob	88	*	*	*	*
6	Jim	73	*	*	*	*
7	Rob	89	*	*	*	*
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

 

[Akashwani]

Hi Herbert,

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

Ak

 

[Herbiec09]

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

 

[Herbiec09]

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

 

[Akashwani]

Hi herbz.

Perhaps something like this...

Excel Workbook
	A	B	C	D	E	F
1	Name	Task Done	70	85	Name
2	Bill	70			Bill
3	Ben	90			Joe
4	Joe	83			Jim
5	Bob	88
6	Jim	73
7	Rob	89
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