Is there a way to link cells between sheets so data can be updated on either sheet?

Sara Ross

I manage requests my organization receives with a main sheet and a couple of sub sheets. Specifically, a form is utilized to generate a new request line item on the main sheet. Then if certain criteria are met, the row is copied to a sub sheet. Unfortunately, the information in the copied row does not update if data changes in the main sheet nor does the main sheet info update if changes are made on the sub sheet. For example, I'd like to be able to enter the request's completion date on either sheet and it be reflected on both sheets. Is this possible?

Michelle Choate 2

The correct answer here is Kind of? You would need helper columns in each sheet. So you would want "Completion Date" and "Completion Date Helper 1" and "Completion Date Helper 2" added to both sheets.

Then Have "Completion Date Helper 1" Be where people can enter data.

Then "Completion Date Helper 2" is where you would have an INDEX/MATCH Formula to be brought in from the other sheet. So it would be a column formula of something like = INDEX({Completion Date Helper 1}, MATCH(Primary@row, {Other Sheet Primary Column},0).

Then finally, the "Completion Date" column would contain a simple column formula of =JOIN([Completion Date Helper 1]@row:[Completion Date Helper 2]@row.

Paul Newcome

@Michelle Choate 2 I would suggest an INDEX/COLLECT instead of the JOIN function though. The issue with the JOIN function is that it turns all output into a text string. So it will look like a date, but it won't work like a date if it is being used in other calculations, sorting, automations, etc..

If you use an INDEX/COLLECT to grab whichever one is not blank, it will retain its date formatting on the back-end.

=IFERROR(INDEX(COLLECT([Completion Date Helper 1]@row:[Completion Date Helper 2]@row, [Completion Date Helper 1]@row:[Completion Date Helper 2]@row, @cell <> ""), 1), "")

@Sara Ross Have you thought about using a Move Row automation instead and then using a report to join all of the sheets together?

Michelle Choate 2

Ah - @Paul Newcome You are correct - I always forget that about JOIN. You keep getting me today!!

She is already doing the copying of rows between sheets, but wants all the data to be in one row across, rather than in different lines in a report I believe.

Sara Ross

Thank you for the guidance. I was hoping it was a little less complicated, but I'll give it a try.