Share via

How do I auto populate a row based on info entered into a column?

Anonymous
2023-04-04T09:21:05+00:00

I have a spreadsheet as below. The first 3 columns need to have data entered and then I need the row to auto populate based on the data entered into column 3. Looked everywhere and can't find how I would do this. Any help would be appreciated.

Time in Time out Vehicle reg Full Name Reason for visit i.e staff, tankers, deliveries, site works Contact on site Areas of site working on Company
06:25 14:34 HF1**** garry gebber WORK OFFICE SITE WWSL
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-04-04T10:07:17+00:00

    Hi Kim P!

    To auto-populate a row based on the data entered into a column, you can use Excel's IF function along with other functions like VLOOKUP, INDEX, and MATCH.

    Here's an example of how you could use these functions to auto-populate a row based on the data entered into column 3 ("Vehicle reg"):

    In cell D2 (the first cell in the "Full Name" column), enter the following formula: =IF(C2<>"",VLOOKUP(C2,Table1,2,FALSE),"") This formula checks if cell C2 ("Vehicle reg") is not empty. If it's not empty, it uses the VLOOKUP function to look up the value in cell C2 in a table called "Table1" (which you'll need to set up with the relevant data). The VLOOKUP function returns the corresponding value from the second column of "Table1". If cell C2 is empty, the formula returns an empty string.

    In cell E2 (the first cell in the "Reason for visit" column), enter the following formula: =IF(C2<>"",VLOOKUP(C2,Table2,2,FALSE),"") This formula is similar to the one in step 1, but it looks up the value in cell C2 in a different table called "Table2", which contains the reasons for visit.

    Repeat this process for the other columns you want to auto-populate, using the appropriate tables and formulas.

    Note that you'll need to set up the tables with the relevant data before you can use the VLOOKUP function. Once you've set up the tables and entered the formulas, you can simply enter the data into column 3 and the other columns should auto-populate based on the data you've entered.

    Kindly let me know, if you require additional assistance, I will be glad to help further.

    Best Regards, Shakiru

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  2. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2023-04-04T10:07:45+00:00

    If you want to get only one information (cell) from the other sheet you can use
    B2: =VLOOKUP(A2,Sheet1!A:B,2,0)

    https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

    If the information is in a different column, e.g. column K, use MATCH/INDEX
    B2: =INDEX(Sheet1!K:K,MATCH(A2,Sheet1!A:A,0))

    If you want to get more information's from the other sheet, I suggest to use MATCH in a helper column and use the cell reference within INDEX, because the term has to be searched only once.
    B2: =MATCH(A2,Sheet1!A:A,0)
    C2: =INDEX(Sheet1!B:B,$B2)
    drag C2 to the right till the end of your data.

    https://support.office.com/en-us/article/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a
    https://support.office.com/en-us/article/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd

    Andreas.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments