I have been searching for 2 hours and can not find anything to help. I would like to have a worksheet with all employees last name, first name, occupational code, phone number and alternate phone number on one page. On different worksheets will be different facilities where the employee can work. What I would like to be able to do on the different worksheets for the facilities is to type the last name of the employee and the first name, occupational code, phone number and alternate phone number automatically fill in. I have tried vlookup and have failed miserably. Can anyone point me in the right direction? Much thanks in advance.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Employee Lists
- Thread starter EF6
- Start date
Sheet 1 would have the following information:
A B C D E
1 Last Name First Name OCC Phone Number Alt Number
2 Doe Jane RN 123-4567 987-6543
3 Doe John LPN 456-7896
Sheet 2 (and so on) would have the same column headings:
A B C D E
1 Last Name First Name OCC Phone Number Alt Number
2
(Sheet 1 & 2 are in the same workbook)
What I would like to achieve is that when I type in DOE in A2 on sheet 2, it will automatically fill in the rest of the columns that correspond to Doe in the appropriate places (columns).
Thanks for the interest!
A B C D E
1 Last Name First Name OCC Phone Number Alt Number
2 Doe Jane RN 123-4567 987-6543
3 Doe John LPN 456-7896
Sheet 2 (and so on) would have the same column headings:
A B C D E
1 Last Name First Name OCC Phone Number Alt Number
2
(Sheet 1 & 2 are in the same workbook)
What I would like to achieve is that when I type in DOE in A2 on sheet 2, it will automatically fill in the rest of the columns that correspond to Doe in the appropriate places (columns).
Thanks for the interest!
Upvote
0
HalfAce
MrExcel MVP
- Joined
- Apr 6, 2003
- Messages
- 9,456
Hello EF6,
For what you're describing vlookup would be perfect except in your example you have a duplicate last name (Doe) and this will present a problem in that vlookup will always return the first one it finds.
Will duplicate last names be an issue in your real list?
For what you're describing vlookup would be perfect except in your example you have a duplicate last name (Doe) and this will present a problem in that vlookup will always return the first one it finds.
Will duplicate last names be an issue in your real list?
Upvote
0
Krishnakumar
Well-known Member
- Joined
- Feb 28, 2003
- Messages
- 2,615
Hi,
Try,
In B2 and copied down & across,
=IF(ISNUMBER(MATCH($A2,Sheet1!$A$2:$A$65536,0)),VLOOKUP($A2,Sheet1!$A$2:B$65536,COLUMNS($A$2:A$2)+1,0),"Not Found")
If you want multiple answers to return then,
In F2 on Sheet2,
=COUNTIF(Sheet1!A:A,A2)
In B2 and copied down & across,
=IF(ROWS($A$2:$A2)<=$F$1,INDEX(Sheet1!B$1:B$10,SMALL(IF(Sheet1!$A$2:$A$10=$A$2,ROW(Sheet1!$A$2:$A$10)),ROWS($A$2:$A2))),"")
Confirmed with Ctrl+Shift+Enter not with just Enter.
HTH
Try,
In B2 and copied down & across,
=IF(ISNUMBER(MATCH($A2,Sheet1!$A$2:$A$65536,0)),VLOOKUP($A2,Sheet1!$A$2:B$65536,COLUMNS($A$2:A$2)+1,0),"Not Found")
If you want multiple answers to return then,
In F2 on Sheet2,
=COUNTIF(Sheet1!A:A,A2)
In B2 and copied down & across,
=IF(ROWS($A$2:$A2)<=$F$1,INDEX(Sheet1!B$1:B$10,SMALL(IF(Sheet1!$A$2:$A$10=$A$2,ROW(Sheet1!$A$2:$A$10)),ROWS($A$2:$A2))),"")
Confirmed with Ctrl+Shift+Enter not with just Enter.
HTH
Upvote
0
HalfAce
MrExcel MVP
- Joined
- Apr 6, 2003
- Messages
- 9,456
OK, try this.
Say your original list is in columns A:E of Sheet1. (starting in row2)
In cell B2 of sheet2, write (or paste) in this formula:
(Note - change the E30 in these formulas to the last row of your data in sheet1)
=IF(A2="","",VLOOKUP(A2,Sheet1!A2:E30,2))
In C2, paste this:
=IF(A2="","",VLOOKUP(A2,Sheet1!A2:E30,3))
In D2. . .
=IF(A2="","",VLOOKUP(A2,Sheet1!A2:E30,4))
And in E2. . .
=IF(A2="","",VLOOKUP(A2,Sheet1!A2:E30,5))
Now when you enter a name from columnA of sheet1 into cell A2 of sheet2
all the data from the other columns will display in sheet2.
You can copy these down as far as needed and to the other sheets as needed.
Be aware that lots of vlookup formulas can have a noticeable effect on file size
& calculation time.
Say your original list is in columns A:E of Sheet1. (starting in row2)
In cell B2 of sheet2, write (or paste) in this formula:
(Note - change the E30 in these formulas to the last row of your data in sheet1)
=IF(A2="","",VLOOKUP(A2,Sheet1!A2:E30,2))
In C2, paste this:
=IF(A2="","",VLOOKUP(A2,Sheet1!A2:E30,3))
In D2. . .
=IF(A2="","",VLOOKUP(A2,Sheet1!A2:E30,4))
And in E2. . .
=IF(A2="","",VLOOKUP(A2,Sheet1!A2:E30,5))
Now when you enter a name from columnA of sheet1 into cell A2 of sheet2
all the data from the other columns will display in sheet2.
You can copy these down as far as needed and to the other sheets as needed.
Be aware that lots of vlookup formulas can have a noticeable effect on file size
& calculation time.
Upvote
0
Thanks to both HalfAce & Kris. I think that I am doing something wrong however. It is only retuning the information fom the first row in the first row. I have copied the formula down several rows, and it is only returning the correct information for row 2. Is there something that I am doing incorrectly?
E
E
Upvote
0
Krishnakumar
Well-known Member
- Joined
- Feb 28, 2003
- Messages
- 2,615
Hi,
Sheet1
Sheet2
Formula in F2,
=COUNTIF(Sheet1!A:A,A2)
In B2 and copied down & across,
=IF(ROWS($A$2:$A2)<=$F$1,INDEX(Sheet1!B$1:B$10,SMALL(IF(Sheet1!$A$2:$A$10=$A$2,ROW(Sheet1!$A$2:$A$10)),ROWS($A$2:$A2))),"")
Confirmed with Ctrl+Shift+Enter
HTH
Sheet1
| Book1 | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | Last Name | First Name | OCC | Phone Number | Alt Number | ||
| 2 | Doe | Jane | RN | 123-4567 | 987-6543 | ||
| 3 | Doe | John | LPN | 456-7896 | |||
Sheet1 | |||||||
Sheet2
| Book1 | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | Last Name | First Name | OCC | Phone Number | Alt Number | 2 | ||
| 2 | Doe | Jane | RN | 123-4567 | 987-6543 | |||
| 3 | John | LPN | 456-7896 | 0 | ||||
| 4 | ||||||||
| 5 | ||||||||
Sheet2 | ||||||||
Formula in F2,
=COUNTIF(Sheet1!A:A,A2)
In B2 and copied down & across,
=IF(ROWS($A$2:$A2)<=$F$1,INDEX(Sheet1!B$1:B$10,SMALL(IF(Sheet1!$A$2:$A$10=$A$2,ROW(Sheet1!$A$2:$A$10)),ROWS($A$2:$A2))),"")
Confirmed with Ctrl+Shift+Enter
HTH
Upvote
0
Similar threads
- Question
