How to mirror a Column from Master Sheet

Karisa Ely

Hello!

I am stuck and not sure what else to try. I have a master sheet where we collect all of our projects. The Project column is used across several other sheets and we currently have to manually update every time a project is added or updated. 

Is there a way to update all Project columns in the other sheets based on the Project column of the master sheet? Workflow? API?

Some sheets I do not need all of the projects, but just projects for a certain season. For that, we would search for any project associated with the particular season. (Example S20 or 2020 Spring depending on which column we need to search from)

 Screenshot is attached with an example sheet of how ours is set up.

• Cell linking works until new rows are added.
• The formulas I have tried will only return the first match.
• Copying Row will not work since I do not need all of the additional columns.

 

Paul Newcome

Have you looked into pulling reports instead of additional sheets?

Karisa Ely

@Paul Newcome Yes, forgot to add that. I have tried reports, but I'm either not report savvy or it doesn't fit what I need.

The project column is only populated on the master sheet and the only common column across the different sheets. I create a report to include the master sheet and the sheet I want to update. Then when I add in the columns I want to add information to, I cannot edit them.

Paul Newcome

Ok. So... There is a way to replicate the Project column, but it requires a little bit of planning ahead and will not be able to replicate the hierarchy.

So if you have

1

1.1

1.2

1.3

2

2.1

2.2

On the master and pulling over to the other sheets, you will have to manually indent each of the child rows beneath the parent rows to match the master. If you happen to add another row somewhere that needs to be indented on the master, you will have to go to each of the other project sheets and manually adjust the indentations there as well.

Karisa Ely

Ok. The indenting we can work with. So if I add a new row in the middle, will it add in the same location on each sheet, just not indented?

Paul Newcome

That is correct. We are going to build almost an exact mirror. Inserting new rows, removing rows, and even sorting rows will all be matched. It is only the indentation that is going to have to be manually managed.

First we need to do the "planning ahead" portion. How many rows do you anticipate needing? I am going to suggest that you add 25% to whatever that number is just in case.

Go ahead and throw something into the Primary Column of the sheet on all of those rows. It doesn't matter what it is. We just need something. We are going to delete it later and replace it with the data from the master sheet. We just need to "pre-fill" the rows you want to use.

Next, insert an auto-number column ("Auto") with no special formatting.

Then insert a text/number column ("Row") with the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Save the sheet.

Delete the data from the Primary Column and apply this column formula:

=INDEX({Master Sheet Primary Column}, Row@row)

Karisa Ely

Thanks Paul! This is great!

However.. :) The formula is working great, and it's everything we need. But we will add new columns to that sheet with information for each project. When a new project is added on the master sheet and updates on the other sheet, it only updates that column. So the data in the other columns do not move up or down with the corresponding project. So it doesn't insert an entire row basically...

Paul Newcome

Yes. That is one of the drawbacks. either you would need to replicate the formula for the rest of the columns, or we would need to look into reports.

You mentioned that you aren't able to get them to work the way you need. Is it a matter of not pulling the correct data in or something else?

Karisa Ely

Ok. I am not sure on the reports.. I'm trying to find some good training on pulling in multiple sheets. I'm not sure that I'm setting it up correctly. Any guidance ?

Paul Newcome

If all sheets are set up exactly the same (same column names primarily) then you have a couple of options.

You can either create the report and select each sheet individually which would require you to manually add new sheets to the report as they are created or you can have all of the sheets you want to reference stored in the same workspace and then reference the workspace. Referencing the workspace means that as new sheets are added there, they will also be automatically added to the report.

Karisa Ely

Thanks @Paul Newcome! I'm going to see if I can get the reports option to work. I really appreciate your help and prompt responses!

Paul Newcome

Happy to help. 👍️