Excel help

[jayesh]

I have list of employees and their department. Column A contains the Department Name, B has name C has address D has phone and E has email.
There is a list of 200 of them.
Is there a way that if I type dept name in other sheet and i can get the list of all the employees with all the column B thru E?

 

[texasalynn]

ok so here you have the first sheet

Excel Workbook
	A	B	C	D	E
1	Department	Name	Address	Phone	Email
2	Accounting	Joe Smith	123 A Street	281-555-7171	Smith@abc.com
3	Finance	Karl Reiner	355 City Avenue	713-555-1122	Reiner@abc.com
4	Sales	Bob Ricker	1515 County Line	832-555-4000	Ricker@abc.com
5	Customer Service	Frank Ling	350 City Court Ln	281-555-7172	Ling@abc.com
6	Collections	Hooker Mannis	311 Loveland Ave	713-555-1123	Mannis@abc.com
7	HR	Carol Lovely	1666 Constance Dr	832-555-4001	Lovely@abc.com
8	Executive	Bill Jobs	1124 A Street	281-555-7173	Jobs@abc.com
9	Accounting	Steve Gates	3356 City Avenue	713-555-1124	Gates@abc.com
10	Finance	Mary Pierce	15700 County Line	832-555-4002	Pierce@abc.com
11	Sales	Connie Gaston	2351 City Court Ln	281-555-7174	Gaston@abc.com
12	Customer Service	Arnold Palmer	3312 Loveland Ave	713-555-1125	Palmer@abc.com
13	Collections	Tiger Woods	1697 Constance Dr	832-555-4003	Woods@abc.com
14	HR	Barbra Wings	1525 A Street	281-555-7175	Wings@abc.com
15	Executive	Carl Reins	4357 City Avenue	713-555-1126	Reins@abc.com
16	Accounting	Debbie Anston	7517 County Line	832-555-4004	Anston@abc.com
17	Finance	Debbie Winston	2350 City Court Ln	281-555-7176	Winston@abc.com
18	Sales	Ken Smith	5310 Loveland Ave	713-555-1127	Smith@abc.com
19	Customer Service	Phil Perry	8670 Constance Dr	832-555-4005	Perry@abc.com
20	Collections	Tom Longfellow	4143 A Street	281-555-7177	Longfellow@abc.com
21	HR	Terri Mercer	7315 City Avenue	713-555-1128	Mercer@abc.com
22	Executive	Bonnie Rath	6522 County Line	832-555-4006	Rath@abc.com
Open

Excel 2003


then for the second sheet you setup like this

Excel Workbook
	A	B	C	D	E
1	Match Dept	Accounting	3
2	Department	Name	Address	Phone	Email
3	Accounting	Joe Smith	123 A Street	281-555-7171	Smith@abc.com
4	Finance	Karl Reiner	355 City Avenue	713-555-1122	Reiner@abc.com
5	Sales	Bob Ricker	1515 County Line	832-555-4000	Ricker@abc.com
6
Sheet2

Excel 2003

Cell Formulas
Range	Formula
C1	=COUNTIF(Open!$A$2:$A$16,B1)

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
copy the formula down to several rows, it feels in blanks for the rows that aren't needed

 

[anglais428]

use a pivot table for this

 

[jayesh]

I did what you gave but the result is what you have shown. If I select accounting then I should only get list of person in accounting and nothing else.

 

[texasalynn]

ADVERTISEMENT

sorry about that the data has to be sorted

Excel Workbook
	A	B	C	D	E
1	Department	Name	Address	Phone	Email
2	Accounting	Joe Smith	123 A Street	281-555-7171	Smith.J@abc.com
3	Accounting	Steve Gates	3356 City Avenue	713-555-1124	Gates.S@abc.com
4	Accounting	Debbie Anston	7517 County Line	832-555-4004	Anston.D@abc.com
5	Collections	Hooker Mannis	311 Loveland Ave	713-555-1123	Mannis.H@abc.com
6	Collections	Tiger Woods	1697 Constance Dr	832-555-4003	Woods.T@abc.com
7	Collections	Tom Longfellow	4143 A Street	281-555-7177	Longfellow.T@abc.com
8	Customer Service	Frank Ling	350 City Court Ln	281-555-7172	Ling.F@abc.com
9	Customer Service	Arnold Palmer	3312 Loveland Ave	713-555-1125	Palmer.A@abc.com
10	Customer Service	Phil Perry	8670 Constance Dr	832-555-4005	Perry.P@abc.com
11	Executive	Bill Jobs	1124 A Street	281-555-7173	Jobs.B@abc.com
12	Executive	Carl Reins	4357 City Avenue	713-555-1126	Reins.C@abc.com
13	Executive	Bonnie Rath	6522 County Line	832-555-4006	Rath.B@abc.com
14	Finance	Karl Reiner	355 City Avenue	713-555-1122	Reiner.K@abc.com
15	Finance	Mary Pierce	15700 County Line	832-555-4002	Pierce.M@abc.com
16	Finance	Debbie Winston	2350 City Court Ln	281-555-7176	Winston.D@abc.com
17	HR	Carol Lovely	1666 Constance Dr	832-555-4001	Lovely.C@abc.com
18	HR	Barbra Wings	1525 A Street	281-555-7175	Wings.B@abc.com
19	HR	Terri Mercer	7315 City Avenue	713-555-1128	Mercer.T@abc.com
20	Sales	Bob Ricker	1515 County Line	832-555-4000	Ricker.B@abc.com
21	Sales	Connie Gaston	2351 City Court Ln	281-555-7174	Gaston.C@abc.com
22	Sales	Ken Smith	5310 Loveland Ave	713-555-1127	Smith.K@abc.com
Open

Excel 2003

Excel Workbook
	A	B	C	D	E
1	Match Dept	Accounting	3
2	Department	Name	Address	Phone	Email
3	Accounting	Joe Smith	123 A Street	281-555-7171	Smith.J@abc.com
4	Accounting	Steve Gates	3356 City Avenue	713-555-1124	Gates.S@abc.com
5	Accounting	Debbie Anston	7517 County Line	832-555-4004	Anston.D@abc.com
6
Sheet2

Excel 2003

Cell Formulas
Range	Formula
C1	=COUNTIF(Open!$A$2:$A$16,B1)
A3	=IF(ROWS(A$3:A3)<=$C$1,INDEX(Open!A$2:A$22,SMALL(IF(Open!$A$1:$E$1=A$2,ROW(Open!$A$2:$A$22)-ROW($A$2)+1),ROWS(A$3:A3))),"")

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[jayesh]

I have office 2007. dose it make any difference?

 

[texasalynn]

ADVERTISEMENT

no it is still necessary to sort

 

[jayesh]

no it is still necessary to sort

It worked. But if I change the department it dose not change the result.

 

[texasalynn]

I changed the formula some:

Excel Workbook
	A	B	C	D	E
1	Match Dept	Collections	3
2	Department	Name	Address	Phone	Email
3	Collections	Hooker Mannis	311 Loveland Ave	713-555-1123	Mannis.H@abc.com
4	Collections	Tiger Woods	1697 Constance Dr	832-555-4003	Woods.T@abc.com
5	Collections	Tom Longfellow	4143 A Street	281-555-7177	Longfellow.T@abc.com
6
7
8
Sheet2

Excel 2003

Cell Formulas
Range	Formula
C1	=COUNTIF(Open!$A$2:$A$16,B1)

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[jayesh]

Thanks a LOT.