Formula for High Value
 

I have 2 columns of data (1st column is name, 2nd column is value) and want
to filter the data so that the name appears on a separate sheet for those
individuals whose value exceeds a certain number. Any ideas would be greatly
appreciated. TIA.

Formula for High Value
 

Data Filter Autofilter.....then click the arrow at the top of the value
column and choose Custom is greater than set the value to exceed ok

When finished, Data Filter Autofilter again to return to normal.........

Vaya con Dios,
Chuck, CABGx3



"Jessinat" wrote in message
...
I have 2 columns of data (1st column is name, 2nd column is value) and
want
to filter the data so that the name appears on a separate sheet for those
individuals whose value exceeds a certain number. Any ideas would be
greatly
appreciated. TIA.

Formula for High Value
 

=IF(ISERR(SMALL(IF(Sheet1!$B$2:$B$30010,ROW(INDIR ECT("1:"&ROWS(Sheet1!$A$2:$A$300)))),ROWS($1:1))), "",INDEX(Sheet1!$A$2:$A$300,SMALL(IF(Sheet1!$B$2:$ B$30010,ROW(INDIRECT("1:"&ROWS(Sheet1!$A$2:$A$300 )))),ROWS($1:1))))

Adjust your range and your criteria to suit.
ctrl+shift+enter, not just enter


"Jessinat" wrote:

I have 2 columns of data (1st column is name, 2nd column is value) and want
to filter the data so that the name appears on a separate sheet for those
individuals whose value exceeds a certain number. Any ideas would be greatly
appreciated. TIA.

Formula for High Value
 

I guess I am confused on what it is looking for, here is my data

Smith, John 4871 82 55 210
Doe, Joe 4740 73 45 290
Doe, Jane 4733 72 31 285
Smith, Bob 4755 72 31 274
David, Bill 4827 72 47 242
Jones, Mike 4722 70 42 307
Allen, Bill 4865 69 37 311
Smith, Jane 4816 68 53 285

I want the names from column A to only be populated on separate worksheet
when the value in column E is greater than 300.

"Teethless mama" wrote:

=IF(ISERR(SMALL(IF(Sheet1!$B$2:$B$30010,ROW(INDIR ECT("1:"&ROWS(Sheet1!$A$2:$A$300)))),ROWS($1:1))), "",INDEX(Sheet1!$A$2:$A$300,SMALL(IF(Sheet1!$B$2:$ B$30010,ROW(INDIRECT("1:"&ROWS(Sheet1!$A$2:$A$300 )))),ROWS($1:1))))

Adjust your range and your criteria to suit.
ctrl+shift+enter, not just enter


"Jessinat" wrote:

I have 2 columns of data (1st column is name, 2nd column is value) and want
to filter the data so that the name appears on a separate sheet for those
individuals whose value exceeds a certain number. Any ideas would be greatly
appreciated. TIA.

Formula for High Value
 

Like I said previous reply you have to modify to suit your needed

Use my previous reply, all you need to change the range in column B to range
E2:E300 what ever your range is, then change the 10 to 300. Also change the
range in column A to what ever your range is. When you done edit make sure
press ctrl+shift+enter



"Jessinat" wrote:

I guess I am confused on what it is looking for, here is my data

Smith, John 4871 82 55 210
Doe, Joe 4740 73 45 290
Doe, Jane 4733 72 31 285
Smith, Bob 4755 72 31 274
David, Bill 4827 72 47 242
Jones, Mike 4722 70 42 307
Allen, Bill 4865 69 37 311
Smith, Jane 4816 68 53 285

I want the names from column A to only be populated on separate worksheet
when the value in column E is greater than 300.

"Teethless mama" wrote:

=IF(ISERR(SMALL(IF(Sheet1!$B$2:$B$30010,ROW(INDIR ECT("1:"&ROWS(Sheet1!$A$2:$A$300)))),ROWS($1:1))), "",INDEX(Sheet1!$A$2:$A$300,SMALL(IF(Sheet1!$B$2:$ B$30010,ROW(INDIRECT("1:"&ROWS(Sheet1!$A$2:$A$300 )))),ROWS($1:1))))

Adjust your range and your criteria to suit.
ctrl+shift+enter, not just enter


"Jessinat" wrote:

I have 2 columns of data (1st column is name, 2nd column is value) and want
to filter the data so that the name appears on a separate sheet for those
individuals whose value exceeds a certain number. Any ideas would be greatly
appreciated. TIA.

Formula for High Value
 

Teethless,

Thanks for your help but Still no luck, have this formula in place and no
results
=IF(ISERR(SMALL(IF(Sheet1!$E$2:$E$300350,ROW(INDI RECT("1:"&ROWS(Sheet1!$E$2:$E$300)))),ROWS($1:2))) ,"",INDEX(Sheet1!$A$2:$A$300,SMALL(IF(Sheet1!$E$2: $E$300350,ROW(INDIRECT("1:"&ROWS(Sheet1!$A$2:$A$3 00)))),ROWS($1:2))))

Would it be possible to email you the 1 sheet to you to see if you see my
stupidity?



"Jessinat" wrote:

I have 2 columns of data (1st column is name, 2nd column is value) and want
to filter the data so that the name appears on a separate sheet for those
individuals whose value exceeds a certain number. Any ideas would be greatly
appreciated. TIA.

Formula for High Value
 

Teethless,

Thank you, thank you, thank you. I figured it out and this is great, I
wasn't CSEing.


"Jessinat" wrote:

I have 2 columns of data (1st column is name, 2nd column is value) and want
to filter the data so that the name appears on a separate sheet for those
individuals whose value exceeds a certain number. Any ideas would be greatly
appreciated. TIA.