Data Mesh: Source vs Target sheets

Meredith Rhodes

Hi all,

I'm creating a work flow to integrate a number of services that my team offers. There is a work sheet that captures all projects sent our way and assigns them to 1 to 3 services - and those projects show up on their respective work sheets via Data Shuttle.

Currently - I have cross sheet formulas set up such that any change to the main work sheet (the current source sheet) will show up on the service work sheets. For data integrity, I've locked columns on the service work sheets sourced from the main sheet.

However, are cells (cells in a column, not the entire column) on the main source sheet that the services would like to be able to update from their sheets. I've been experimenting with Data Mesh a bit - and have been unsuccessful in linking individual cells from the service sheet to the main sheet.

Will cell linkage only work on the entire column?

Is there a way to link more than one sheet together for a dynamic view such that the service line's workflow is from one single view rather than updating two different sheets?

I would love to find a resource that explains the pros and cons of Column Formulas vs Data Mesh - or tips for data integrity in general.

Thank you!

Meredith

Paul Newcome

Are you able to provide some screenshots with sample data for reference? There are a number of different ways to bring multiple sheets together, but there are a number of variables (volume, structure, etc.) that also come into play.

Meredith Rhodes

Thanks Paul,

Let me see if these visuals help:

This a screen shot from my main sheet - where each new project (we're running clinical trials) is documented with a Unique ID and the service(s) selected (Fiscal, Study Coordination, Regulatory). There are currently 300 rows and about 55 columns on this sheet - this could grow significantly in the coming years - unless we decide to move completed studies onto another sheet, in which case, that sheet will grow (but perhaps in an archival format). When a new Unique ID is added and a service is selected, it copies the Unique ID to the relevant service's sheet. Here you see Unique ID 0002, 0004, and 0005 would like Study Coordination.

But the other studies (0001, 0003, 0006, 0007) also have Study Coordinators documented in the main sheet - they are just not from our service. We do not want their contact information overwritten.

Here is what this looks like on the Study Coordinators tracker, just rows 0002, 0004, and 0005. They would love to be able to assign their coordinators on their tracker and have it populate the main tracker (right now, I've got cross sheet formulas - so the have to input the data on the other sheet).

Is there a way to link ONLY the Primary Coordinator Cells from their tracker back to the main tracker such that they can update this information from the sheet they primarily work on?

I hope this helps!

Meredith

Paul Newcome

Have you looked into having them work in a report?

Meredith Rhodes

I have created a couple of dynamic views for them, and plopped them on a dashboard. Can you describe the benefit of a report?

Paul Newcome

The benefit to the report is that they would be able to make their updates and the report could be filtered to only show those certain rows, but you can also apply a filter to a dynamic view.