# Formula to auto-fill data from another sheet

1. ## Formula to auto-fill data from another sheet

Sheet 1: Master list that has column headings of various employee info (names, ID, address,etc).

Sheet 2: Same column headings as Sheet 1, but only the first/last names are filled out.

I need a formula so that when I'm in sheet 2 and I type in a last name, it auto fills from the master list of employee info on sheet 1. So in sheet 2, I have first and last names filled out for 10 employees out of the master list of about 50 employees, but I need it to populate the data to include ID, address, etc).

Can anyone help me with this please?

To clarify, I'm entering weekly hours for payroll. For my report, I need all of these column headings existing in the Master sheet 1. However, I only have the first and last names and their hours in front of me and I need it to populate the data form sheet 1 into sheet 2 when I type in the names. Note, only 10-15 employees work each week and it always varies.

2. ## Re: Formula to auto-fill data based on existing data in other sheet/table

It would help if you attached a sample Excel workbook, by following the instructions given in the yellow banner at the top of the screen.

Hope this helps.

Pete

3. ## Re: Formula to auto-fill data based on existing data in other sheet/table

Just attached the file. Thanks for the reply.

4. ## Re: Formula to auto-fill data based on existing data in other sheet/table

You can use this formula in cell A2 of Sheet2:

=IFERROR(INDEX('Master Sheet 1'!A\$2:A\$101,MATCH(1,('Master Sheet 1'!\$B\$2:\$B\$101=\$B2)*('Master Sheet 1'!\$C\$2:\$C\$101=\$C2),0)),"")

then copy it down to the bottom of your list of names. Note that there is a blank returned for Crosby|Kenneth in row 6 - that is because you have a trailing space after Crosby in B6, so if you remove that then you will get the ID as 666. There is also a blank in cell A8 for Tom Tekampe, and that is because the matching name that you are looking for in the Master sheet is Thomas Tekampe. The names have to be exactly the same for there to be a match. Once you have made the corrections, then you can copy the formulae across into columns D to F.

Hope this helps.

Pete

5. ## Re: Formula to auto-fill data from another sheet

Yep, got it. Awesome, thanks for the help!

6. ## Re: Formula to auto-fill data from another sheet

As you are relatively new to the forum, you might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This adds to the reputation of the poster (the small green bars in the poster's profile).

Can you also update your profile in User CP to show the version of MS Office that you are using - Windows 10 is not MS Office.

Pete

There are currently 1 users browsing this thread. (0 members and 1 guests)