Copying select information from a row to another sheet

Colleen Branigan

Is there a way to auto copy selected columns to a single budgeting sheet without manual cell linking?

We use multiple sheets to plan and track our ongoing projects but need key pieces of information brought over to a master sheet for budgeting. The individual sheets are used by our marketing department for planning events and the master sheet is used by our accounting department. The marketing department sheets have much more information (columns) than the accounting department needs and yet does not include key information that needs to be added/manipulated by accounting.

We can't use a report as accounting needs to have the ability to add additional information to the master budget sheet in columns that do not exist on the individual sheets.

It would be perfect if there was a way to auto copy rows and select the columns I want to copy rather than copy the entire row?

Thanks Colleen

Andrée Starå

Hi @Colleen Branigan

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Colleen Branigan

Hi Andree,

I love your idea of the cross sheet formulas so that it is bi-directional with vlookup but how do I specify my range with multiple sheets that we are continuously adding to? This would be perfect if I could use vlookup to reference a report but I don't seem to be able to use a report in my range?

Colleen

Andrée Starå

@Colleen Branigan

Excellent!

How would you add to the range? Can you elaborate?

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

Colleen Branigan

The option you suggested with a helper sheet would work to capture all of the new sheets as they are created and allow me to created a vlookup formula referencing a range on the helper sheet however it would not allow for bidirectional updating.

If I could point the reference range at a report it would continuously capture new sheets that we create over the course of the year.