Automatically copy cells from one sheet to another

Rick Byers

Trying to use VLookup to automatically copy cell data from one sheet to another. I tried to create a function that searches for "Project Name" and "Comments" columns in a sheet titled "Projects" and copies "Comments" to a sheet titled "Campaigns", where the Project Names are exactly the same in both sheets. Can't quite figure out what is needed to get this to flow correctly to another sheet.

Genevieve P.

Hey @Rick Byers

I would recommend using an INDEX(MATCH combination here instead of a VLOOKUP. This reduces the number of cells that are referenced in a formula (making it faster to load) and also references each column individually so you can adjust column locations without breaking the formula.

Here's the structure:

=INDEX({Column to return}, MATCH("Matching Value", {Column with value to match}, 0))

So in your case, something like this:

=INDEX({Comments Column}, MATCH([Project Name]@row, {Project Name Column}, 0))

Here's more information: Formula combinations for cross sheet references

Let me know if that worked for you!

Cheers,

Genevieve

Rick Byers

Hi Genevieve,

I am getting an error message: #NO MATCH with the following formula:

=INDEX({Marketing Project List - test Range 2}, MATCH([Project Name]@row, {Marketing Project List - test Range 1}, 0))

Sheet name I am referencing to pull cell data: "Marketing Project List - test"

"Range 2" = Comments Column

"Range 1" = Project Name Column

I also tried adding another sheet reference just after "MATCH(" referencing the Project Name Column, but that didn't work as well. Thoughts on what I should try next? Thanks!

Genevieve P.

Hey @Rick Byers

That looks correct to me! 🙂

The No Match is telling us that it can't find a match between your two Project Columns. In your screen captures, one sheet says "Project Name 1", whereas the other sheet says "Project 1". Have you updated them to have matching names, or does one still say "Name" in the middle?

Andrée Starå

Hi @Rick Byers

I hope you're well and safe!

Can you share some screenshots and the formula you're trying to use? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Rick Byers

Hi Andree,

I have tried using VLookup to do what I need here, but I am not an expert when it comes to this function. I am attaching a few screenshots. Marketing Project List sheet is the originating sheet from which I want to pull "Funnel Target" and "Comments" and automatically populate in the Campaign Calendar sheet. Here's my attempt at the formula, which obviously doesn't work:

=VLOOKUP({Marketing Project List - test Range 1}, "Project 1", {Marketing Project List - test Range 2}, "comment 1")

Campaign Calendar.png

Marketing Project List.png

The data in each of the cells for "Project Name", "Funnel Target" and "Comments will always be variable. Please let me know what else I can share with you to figure this out. Thanks for your help!

Rick Byers

@andree@workbold.com

Hi Andree,

I have tried using VLookup to do what I need here, but I am not an expert when it comes to this function. I am attaching a few screenshots. Marketing Project List sheet is the originating sheet from which I want to pull "Funnel Target" and "Comments" and automatically populate in the Campaign Calendar sheet. Here's my attempt at the formula, which obviously doesn't work:

=VLOOKUP({Marketing Project List - test Range 1}, "Project 1", {Marketing Project List - test Range 2}, "comment 1")

Campaign Calendar.png

Uploaded Sep 25, 202389.09 KB

Marketing Project List.png

Uploaded Sep 25, 2023112.39 KB

The data in each of the cells for "Project Name", "Funnel Target" and "Comments will always be variable. Please let me know what else I can share with you to figure this out. Thanks for your help!

Genevieve P.

Hey @Rick Byers

I would recommend using an INDEX(MATCH combination here instead of a VLOOKUP. This reduces the number of cells that are referenced in a formula (making it faster to load) and also references each column individually so you can adjust column locations without breaking the formula.

Here's the structure:

=INDEX({Column to return}, MATCH("Matching Value", {Column with value to match}, 0))

So in your case, something like this:

=INDEX({Comments Column}, MATCH([Project Name]@row, {Project Name Column}, 0))

Here's more information: Formula combinations for cross sheet references

Let me know if that worked for you!

Cheers,

Genevieve

Rick Byers

Hi Genevieve,

Thanks for the formula, I'll give it a try!

Rick Byers

Hi Genevieve,

I am getting an error message: #NO MATCH with the following formula:

=INDEX({Marketing Project List - test Range 2}, MATCH([Project Name]@row, {Marketing Project List - test Range 1}, 0))

Sheet name I am referencing to pull cell data: "Marketing Project List - test"

"Range 2" = Comments Column

"Range 1" = Project Name Column

I also tried adding another sheet reference just after "MATCH(" referencing the Project Name Column, but that didn't work as well. Thoughts on what I should try next? Thanks!

Genevieve P.

Hey @Rick Byers

That looks correct to me! 🙂

The No Match is telling us that it can't find a match between your two Project Columns. In your screen captures, one sheet says "Project Name 1", whereas the other sheet says "Project 1". Have you updated them to have matching names, or does one still say "Name" in the middle?

Rick Byers

Genevieve,

That was the issue... once I changed the Project Names the same in both sheets, I was able to get this to work! Thanks for your help!

Darla Brown

Hi @Rick Byers ,

A tip I use - mainly because I don't like the #NO MATCH return - is to add IFERROR to the formula. It would look like this:

=IFERROR(INDEX({Marketing Project List - test Range 2}, MATCH([Project Name]@row, {Marketing Project List - test Range 1}, 0)),"")

Then if there's no match would leave the cell blank.

😊

Rick Byers

Thank you Darla. Nice touch!