Excel help

J

jayesh

New Member
Joined
Mar 6, 2011
Messages
36
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?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
ok so here you have the first sheet
Excel Workbook
ABCDE
1DepartmentNameAddressPhoneEmail
2AccountingJoe Smith123 A Street281-555-7171Smith@abc.com
3FinanceKarl Reiner355 City Avenue713-555-1122Reiner@abc.com
4SalesBob Ricker1515 County Line832-555-4000Ricker@abc.com
5Customer ServiceFrank Ling350 City Court Ln281-555-7172Ling@abc.com
6CollectionsHooker Mannis311 Loveland Ave713-555-1123Mannis@abc.com
7HRCarol Lovely1666 Constance Dr832-555-4001Lovely@abc.com
8ExecutiveBill Jobs1124 A Street281-555-7173Jobs@abc.com
9AccountingSteve Gates3356 City Avenue713-555-1124Gates@abc.com
10FinanceMary Pierce15700 County Line832-555-4002Pierce@abc.com
11SalesConnie Gaston2351 City Court Ln281-555-7174Gaston@abc.com
12Customer ServiceArnold Palmer3312 Loveland Ave713-555-1125Palmer@abc.com
13CollectionsTiger Woods1697 Constance Dr832-555-4003Woods@abc.com
14HRBarbra Wings1525 A Street281-555-7175Wings@abc.com
15ExecutiveCarl Reins4357 City Avenue713-555-1126Reins@abc.com
16AccountingDebbie Anston7517 County Line832-555-4004Anston@abc.com
17FinanceDebbie Winston2350 City Court Ln281-555-7176Winston@abc.com
18SalesKen Smith5310 Loveland Ave713-555-1127Smith@abc.com
19Customer ServicePhil Perry8670 Constance Dr832-555-4005Perry@abc.com
20CollectionsTom Longfellow4143 A Street281-555-7177Longfellow@abc.com
21HRTerri Mercer7315 City Avenue713-555-1128Mercer@abc.com
22ExecutiveBonnie Rath6522 County Line832-555-4006Rath@abc.com
Open
Excel 2003


then for the second sheet you setup like this
Excel Workbook
ABCDE
1Match DeptAccounting3
2DepartmentNameAddressPhoneEmail
3AccountingJoe Smith123 A Street281-555-7171Smith@abc.com
4FinanceKarl Reiner355 City Avenue713-555-1122Reiner@abc.com
5SalesBob Ricker1515 County Line832-555-4000Ricker@abc.com
6
Sheet2
Excel 2003
Cell Formulas
RangeFormula
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
 
Upvote 0
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.
 
Upvote 0
sorry about that the data has to be sorted
Excel Workbook
ABCDE
1DepartmentNameAddressPhoneEmail
2AccountingJoe Smith123 A Street281-555-7171Smith.J@abc.com
3AccountingSteve Gates3356 City Avenue713-555-1124Gates.S@abc.com
4AccountingDebbie Anston7517 County Line832-555-4004Anston.D@abc.com
5CollectionsHooker Mannis311 Loveland Ave713-555-1123Mannis.H@abc.com
6CollectionsTiger Woods1697 Constance Dr832-555-4003Woods.T@abc.com
7CollectionsTom Longfellow4143 A Street281-555-7177Longfellow.T@abc.com
8Customer ServiceFrank Ling350 City Court Ln281-555-7172Ling.F@abc.com
9Customer ServiceArnold Palmer3312 Loveland Ave713-555-1125Palmer.A@abc.com
10Customer ServicePhil Perry8670 Constance Dr832-555-4005Perry.P@abc.com
11ExecutiveBill Jobs1124 A Street281-555-7173Jobs.B@abc.com
12ExecutiveCarl Reins4357 City Avenue713-555-1126Reins.C@abc.com
13ExecutiveBonnie Rath6522 County Line832-555-4006Rath.B@abc.com
14FinanceKarl Reiner355 City Avenue713-555-1122Reiner.K@abc.com
15FinanceMary Pierce15700 County Line832-555-4002Pierce.M@abc.com
16FinanceDebbie Winston2350 City Court Ln281-555-7176Winston.D@abc.com
17HRCarol Lovely1666 Constance Dr832-555-4001Lovely.C@abc.com
18HRBarbra Wings1525 A Street281-555-7175Wings.B@abc.com
19HRTerri Mercer7315 City Avenue713-555-1128Mercer.T@abc.com
20SalesBob Ricker1515 County Line832-555-4000Ricker.B@abc.com
21SalesConnie Gaston2351 City Court Ln281-555-7174Gaston.C@abc.com
22SalesKen Smith5310 Loveland Ave713-555-1127Smith.K@abc.com
Open
Excel 2003
Excel Workbook
ABCDE
1Match DeptAccounting3
2DepartmentNameAddressPhoneEmail
3AccountingJoe Smith123 A Street281-555-7171Smith.J@abc.com
4AccountingSteve Gates3356 City Avenue713-555-1124Gates.S@abc.com
5AccountingDebbie Anston7517 County Line832-555-4004Anston.D@abc.com
6
Sheet2
Excel 2003
Cell Formulas
RangeFormula
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 {}.
 
Upvote 0
I changed the formula some:
Excel Workbook
ABCDE
1Match DeptCollections3
2DepartmentNameAddressPhoneEmail
3CollectionsHooker Mannis311 Loveland Ave713-555-1123Mannis.H@abc.com
4CollectionsTiger Woods1697 Constance Dr832-555-4003Woods.T@abc.com
5CollectionsTom Longfellow4143 A Street281-555-7177Longfellow.T@abc.com
6
7
8
Sheet2
Excel 2003
Cell Formulas
RangeFormula
C1=COUNTIF(Open!$A$2:$A$16,B1)
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Modify a worksheet without registering it as a change
Replies
1
Views
103
Kaper
Kaper
C
  • Question Question
Lookup 2 criteria and return yes or no
Replies
3
Views
126
RoryA
RoryA
A
  • Solved
Pull data from multiple sheets based off name and month selected
Replies
2
Views
128
Amo840812
A
D
  • Locked
  • Question Question
Assistance with IF function
Replies
9
Views
175
Joe4
Joe4
S
  • Question Question
VBA Code that extracts multiple tabs at a time based on lookup tab
2
Replies
10
Views
547
NoSparks
N

Forum statistics

Threads
1,224,521
Messages
6,179,285
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top