Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup?
I have a list of people from different states with names, addresses, etc over
several columns... They're all on the same sheet and the sheet is updated frequently (people are added). I then have a tab for each state and would like each tab to update according to the state found on the main sheet with the info found there (names, addresses, etc). If I do a lookup function, then I only get one result, but I have more than 1 person, say from California... I would like to have all the people from CA show up in the CA tab. No copying and pasting... Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup?
On Mar 25, 9:30 pm, Caroline
wrote: I have a list of people from different states with names, addresses, etc over several columns... They're all on the same sheet and the sheet is updated frequently (people are added). I then have a tab for each state and would like each tab to update according to the state found on the main sheet with the info found there (names, addresses, etc). If I do a lookup function, then I only get one result, but I have more than 1 person, say from California... I would like to have all the people from CA show up in the CA tab. No copying and pasting... Any ideas? Is it possible to add an extra column on the "list-sheet"? If so you could add a column which counts how many times each state i represented. I've simplified somewhat and made a list that look like this: Name State Count John CA =COUNTIF(B$2:B;B2) and copy down. Mary CA 2 Then A2 on the CA sheet would look like this: =INDEX(Sheet1!$A$2:$A$36;MATCH("CA"&ROW()-1;Sheet1!$B$2:$B$36&Sheet1!$C $2:$C$36;0)) Note this is an array formula and should be closed with ctrl+shurt +enter. Then you will get {} around it. BTW if this was confusing, don't worry I am sure someone comes up with a much more clever solution pretty soon. Per Erik |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup?
On the sheet where you have the data, assuming that it is "Sheet1" and you
have names in column A and state in Column B. On the California sheet, you have state name in..say....A2. put the following in the cell where you want the first name to appear. =IF(ROWS(A$2:A2)<=COUNTIF(Sheet1!B$2:B$100,A$1),IN DEX(Sheet1!A$2:A$100,SMALL(IF(Sheet1!B$2:B$100=A$1 ,ROW(Sheet1!A$2:A$100)-MIN(ROW(Sheet1!A$2:A$100))+1),ROWS(A$2:A2))),"") Press CTRL+SHIFT+ENTER and drag it all the way down. hope this helps. Thanks Gaurav "Caroline" wrote in message ... I have a list of people from different states with names, addresses, etc over several columns... They're all on the same sheet and the sheet is updated frequently (people are added). I then have a tab for each state and would like each tab to update according to the state found on the main sheet with the info found there (names, addresses, etc). If I do a lookup function, then I only get one result, but I have more than 1 person, say from California... I would like to have all the people from CA show up in the CA tab. No copying and pasting... Any ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup?
Or use Pivot Tables.
No formulas needed. Automatic update. http://www.freefilehosting.net/download/3e67k |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Advanced Lookup (lookup for 2 values) | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |