+ Reply to Thread
Results 1 to 6 of 6

Formula to auto-fill data from another sheet

  1. #1
    Registered User
    Join Date
    05-20-2021
    Location
    Chicago
    MS-Off Ver
    Windows 10
    Posts
    5

    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.
    Attached Files Attached Files
    Last edited by biguzivert; 05-20-2021 at 11:53 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,511

    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. #3
    Registered User
    Join Date
    05-20-2021
    Location
    Chicago
    MS-Off Ver
    Windows 10
    Posts
    5

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

    Just attached the file. Thanks for the reply.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,511

    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. #5
    Registered User
    Join Date
    05-20-2021
    Location
    Chicago
    MS-Off Ver
    Windows 10
    Posts
    5

    Re: Formula to auto-fill data from another sheet

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

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,511

    Re: Formula to auto-fill data from another sheet

    Glad to help, and thanks for marking the thread as Solved.

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula to auto-fill data from another sheet
    By biguzivert in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-20-2021, 12:03 PM
  2. VBA code to create table auto-fill with data from another tab based on a condition
    By Aleigclara in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 03-19-2021, 12:50 PM
  3. [SOLVED] Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table
    By ITY in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-23-2019, 09:57 AM
  4. Need to Auto Format Sheet based on available data on table
    By dharrier in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-20-2015, 07:06 PM
  5. Auto fill table based on the data from another workbook
    By Biona in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-26-2011, 01:30 AM
  6. Replies: 8
    Last Post: 12-04-2010, 01:20 PM
  7. [SOLVED] How do I use existing data from a worksheet to auto fill a second
    By mac in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-26-2005, 06:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1