Is this possible with any combination of copy/move; index/match, and or cell linking/other?

Kevin Smith

I want to see if it is possible to copy rows from a master sheet in to other sheets and have them sync'd to where the master sheet changes reflect in the rows copied to the other sheets and the changes in the other sheets reflect in the master.

EX: (does not matter what type of col. but Master sheet has 5 cols., 4 are populated. When the 5th is populated or checked, the row will go to a specific sheet (based on a value in one of the cols.)with the same exact cols.. This happens for any number (say 10) sheets. So now I have say 20 rows in the master and a total of lets say 2 rows in each of the 10 sheets, the same 20 rows in the master sheet. All match and all are present in one of the 10 sheets and the master. When someone changes the master, the corresponding sheet changes and when that corresponding sheet changes the master changes. Last change will always be the one presented.

I can't seem to copy rows and have the index/match formula present in the other sheet because the copy considers that data and goes to the next blank row. I don't really want to go in and do the drag formula but if that is the only way, that might have to be it but then the master sheet needs the correct formula and it will not always be for the same sheet, it will differ by row amongst the 10 sheets.

The issue has come about because the owner of the Master does not want the other 10 sheet owners in their sheet so they all need their own sheets. If there was a way to restrict the row in the master sheet to only be edited by the Master sheet owner or the person identified in one of the columns, that might work to resolve the problem as well but I have not see that capability either. Open to suggestions. Thank you all.

Paul Newcome

That is correct. Any updates made in a report are reflected on the source sheet (in this case the master) and the other way around. Any updates made in the master will reflect on the reports.

Paul Newcome

Happy to help. 👍️

Paul Newcome

It is because you are the owner of the sheet and the report. Owners and Admin can edit locked rows/columns.If you are able, set up a test sheet and test report with some "dummy data" and send it to one of the users. Have them try to edit a cell in the locked column and report their findings back to you just to be sure.

Paul Newcome

Do you need to run further metrics on the other sheets, or is it simply for updating rows/viewing? If you don't need to run further metrics on the smaller sheets, you could use Reports to achieve the desired result.

Kevin Smith

Thank you for looking at this with me.

The sheets will be updated by both but as far as metrics, like how long between dates, etc. they would be the same between the Master and the smaller sheets. The metrics will really only be done by rows, there will be some dashboarding done by sheet (both types) but not a lot of metrics, I can see some summary information by sheet but that could be done in the master as well. Let me know if this answers your question, I would be happy to go into more details if needed.

Paul Newcome

Since everything is collected on the master, you can use that for any kind of calculations. I am definitely going to suggest looking into Reports.

Kevin Smith

I agree, all calcs could easily be done in the master. I am not sure how I could utilize reports. Do you mean have the 10 smaller sheet owners use reports to somehow make their updates?

Paul Newcome

That is correct. Any updates made in a report are reflected on the source sheet (in this case the master) and the other way around. Any updates made in the master will reflect on the reports.

Kevin Smith

Let me give that a shot. I will mark this as answered for now but would like to ping it back if needed. Thank you for this, I am very hopeful this will be a good solution. Thank you.

Paul Newcome

Happy to help. 👍️

Kevin Smith

Paul, this works great, but I have a concern that is somewhat related and may be just a reporting thing that I don't understand yet, but how would you handle a field that I want to display on the report but not have them edit or change? I tried locking it in the Master sheet but that did not reflect when it came through to the report or is it just that because I am the owner of Master it let me change it?

Paul Newcome

It is because you are the owner of the sheet and the report. Owners and Admin can edit locked rows/columns.If you are able, set up a test sheet and test report with some "dummy data" and send it to one of the users. Have them try to edit a cell in the locked column and report their findings back to you just to be sure.

Kevin Smith

Thank you

Paul Newcome

Sure thing. 👍️