Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |