Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

# Help with automating data in a table from one sheet to another

Copper Contributor

# Help with automating data in a table from one sheet to another

Hello all,

I am trying to automate data based off a table that gets generated daily. As the cells I am trying to autofill will have the information come off variable rows daily I am wondering if anyone could help.

Essentially it would be having a cell look for a matching name on column D in another sheet. If it finds that name then it would pull the numerical value from column G from the corresponding row where that data in column D was matched from.

I am fairly new to formulas in excel so any advice will be greatly appreciated. Apologies if my terminology seems vague.

2 Replies
best response confirmed by Jase-R (Copper Contributor)
Solution

# Re: Help with automating data in a table from one sheet to another

Many roads lead to Rome, here is one of the roads. You can achieve this using the VLOOKUP function in Excel. The VLOOKUP function searches for a value in the first column of a range and returns a value in the same row from another column you specify. Here is how you can use it in your scenario:

Assuming:

• Sheet1 contains your daily generated table with names in column D and numerical values in column G.
• Sheet2 is where you want to autofill the data.

In Sheet2, you can use the following formula in the cell where you want the numerical value to appear:

=VLOOKUP(A2, Sheet1!\$D\$2:\$G\$100, 4, FALSE)

Here is what each part of the formula does:

• A2 is the cell containing the name you want to search for.
• Sheet1!\$D\$2:\$G\$100 is the range where Excel should look for the name (column D) and the numerical value (column G). Adjust the range to cover all your data.
• 4 specifies that Excel should return the value from the 4th column of the range (column G).
• FALSE ensures that Excel performs an exact match. This means it will only return a value if it finds an exact match for the name.

Drag this formula down to autofill the numerical values for the corresponding names in your list.

Remember to adjust the range (\$D\$2:\$G\$100) to cover all the data in your table on Sheet1. You may also need to adjust the range if your data goes beyond row 100. AI was partially deployed to support the text.

My answers are voluntary and without guarantee!

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

# Re: Help with automating data in a table from one sheet to another

Thanks for your help! This pointed me in the right direction :)
1 best response

Accepted Solutions
best response confirmed by Jase-R (Copper Contributor)
Solution

# Re: Help with automating data in a table from one sheet to another

Many roads lead to Rome, here is one of the roads. You can achieve this using the VLOOKUP function in Excel. The VLOOKUP function searches for a value in the first column of a range and returns a value in the same row from another column you specify. Here is how you can use it in your scenario:

Assuming:

• Sheet1 contains your daily generated table with names in column D and numerical values in column G.
• Sheet2 is where you want to autofill the data.

In Sheet2, you can use the following formula in the cell where you want the numerical value to appear:

=VLOOKUP(A2, Sheet1!\$D\$2:\$G\$100, 4, FALSE)

Here is what each part of the formula does:

• A2 is the cell containing the name you want to search for.
• Sheet1!\$D\$2:\$G\$100 is the range where Excel should look for the name (column D) and the numerical value (column G). Adjust the range to cover all your data.
• 4 specifies that Excel should return the value from the 4th column of the range (column G).
• FALSE ensures that Excel performs an exact match. This means it will only return a value if it finds an exact match for the name.

Drag this formula down to autofill the numerical values for the corresponding names in your list.

Remember to adjust the range (\$D\$2:\$G\$100) to cover all the data in your table on Sheet1. You may also need to adjust the range if your data goes beyond row 100. AI was partially deployed to support the text.

My answers are voluntary and without guarantee!