Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Form a sublist from a list using lookup
I have a table listed as follows:
(A) (B) (C) 1) Name Region Age 2) John America 23 3) Lee Asia 42 4) Johann Europe 18 5) Tom America 43 6) Sara America 19 7) Ricardo Europe 37 I am trying to form 3 new additional tables for each of the three regions from this table that lists only Names of people over 30 yrs old (preferrably in a descending order). I thought of using something like =IF(AND(C230,B2="America"),A2,""), but instead of "", I would want the formula to look into the next rows until it finds one that fits the region, or if it finds none - then it should leave a blank cell. Otherwise, each of the tables would have many blank rows within, between the rows that are of interest. Any ideas of how this could be accomplished? Thank you very much in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Form a sublist from a list using lookup
Consider using a helper column & AutoFilter:
Put a label in D1 In cell D2 enter: =IF(AND(C230,B2="America"),1,0) and copy down Turn on the autofilter on column D ( displaying only 1's) and copy the visible data. -- Gary''s Student - gsnu200795 "Makaron" wrote: I have a table listed as follows: (A) (B) (C) 1) Name Region Age 2) John America 23 3) Lee Asia 42 4) Johann Europe 18 5) Tom America 43 6) Sara America 19 7) Ricardo Europe 37 I am trying to form 3 new additional tables for each of the three regions from this table that lists only Names of people over 30 yrs old (preferrably in a descending order). I thought of using something like =IF(AND(C230,B2="America"),A2,""), but instead of "", I would want the formula to look into the next rows until it finds one that fits the region, or if it finds none - then it should leave a blank cell. Otherwise, each of the tables would have many blank rows within, between the rows that are of interest. Any ideas of how this could be accomplished? Thank you very much in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
filling a form in Excel... lookup? maybe? | Excel Discussion (Misc queries) | |||
Drop Down Sublist | Excel Discussion (Misc queries) | |||
Looking to create a simple user form with lookup | Excel Discussion (Misc queries) | |||
Changing lookup form numbers to alphabetics | Excel Worksheet Functions |