Lookup?

[Caroline]

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?

 

[Per Erik Midtrød]

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

 

[Gaurav]

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),INDEX(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

 

[Herbert Seidenberg]

Or use Pivot Tables.
No formulas needed.
Automatic update.
http://www.freefilehosting.net/download/3e67k