Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have a sheet with columns of clients and percentages. I would like a new
sheet to pull only those client names that exceed a certain percentage i.e. 50% and create a list with only those clients. Is there a lookup function that can do this? thanks loads. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Not sure why you need an extra sheet. Why not switch on AutoFilter and
filter on the percentage column. You could use a simple filter or a custom filter, for example, greater than or equal to 50% That would hide any rows that don't meet the criteria. Just print the sheet while the filter is in. To return to the "normal" view, just choose Show All Regards Trevor "360Kid" wrote in message ... I have a sheet with columns of clients and percentages. I would like a new sheet to pull only those client names that exceed a certain percentage i.e. 50% and create a list with only those clients. Is there a lookup function that can do this? thanks loads. |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Try this:
Sheet1 A2:A10 = client names Sheet1 B2:B10 = percentage Sheet2 A1 = percentage to use as the lookup criteria: 50% Sheet2 A2 enter this formula as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =IF(ROWS(A$1:A1)<=COUNTIF(Sheet1!B$2:B$10,""&A$1) ,INDEX(Sheet1!A$2:A$10,SMALL(IF(Sheet1!B$2:B$10A$ 1,ROW(Sheet1!A$2:A$10)-ROW(Sheet1!A$2)+1),ROWS(A$1:A1))),"") Copy down to A10. Here's a sample file: sample_extract1.xls 15kb http://cjoint.com/?kmagXYXIkn Biff "360Kid" wrote in message ... I have a sheet with columns of clients and percentages. I would like a new sheet to pull only those client names that exceed a certain percentage i.e. 50% and create a list with only those clients. Is there a lookup function that can do this? thanks loads. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
LOOKUP and return the column heading for IF/THEN return for False | Excel Discussion (Misc queries) | |||
formula to lookup value and return value from cell at left of target | Excel Worksheet Functions | |||
Lookup a row and column and return value | Excel Worksheet Functions | |||
INDIRECT lookup of sheet names | Excel Worksheet Functions | |||
Lookup cell value using list of worksheet names | Excel Worksheet Functions |