Help with conditional ongoing merging of multiple spreadsheets

TruCal

I have 3 sheets. 2 are kept up to date with new activities by different group members. the third I want to capture new entries into the other 2 sheets when certain conditions are met. Is there a way to write a lookup to search for new entries and if found add to third sheet with accompanying information?

The data on the "reference" sheets has a unique identifier per activity and will say the purpose.

For criteria where purpose = test I want the activity copied to the 3rd sheet. As and when it is added to the reference sheets

Is that possible?

Genevieve P.

Hi @TruCal

Thank you for clarifying; no, you won't be able to add in additional columns to the Report that don't exist in the underlying sheet.

Depending on how large your source sheet is, you could add these columns to the Sheet then hide them in the sheet and only show them in the Report.

If your sheet is too large to add in these extra columns, then you could use an INDEX(MATCH formula to pull data from one sheet to another, but in this instance you would need to have the matching ID in both sheets before the formula can calculate.

It actually sounds like the best solution would be to use the add-on called DataMesh. This can bring data from one sheet into another (including bringing in the unique ID to match across sheets) and it can auto-create cell-links between the sheets so that your data will stay up to date. I would suggest talking to your System Admin to see if your plan has already purchased DataMesh, and see this article for more information: DataMesh

Cheers,

Genevieve

Andrée Starå

Hi @TruCal

I hope you're well and safe!

You could set up Workflows combined with the Copy Row Action, which copies over to the other sheet for each new row.

Make sense?

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!

TruCal

Hi Andree

same wishes of your health too.

Thanks so much for your help.

I have had a play around with automated workflow but nothing seems to happen.

looks like the only option that might be suitable is copy row. I think my issue is that the destination file is an abridged version of the source plus some additional rows using vlookup to a further sheet.

Could this be the reason why my workflow fails to update? do the source and destination sheets need to be exact copies? or can you define which columns to copy over? the simplest would be to copy over just 1 cell (unique task identifier: to act as search key)and I can pick and chose the other info using a vlookup to populate the other columns. Is that possible?

TruCal

Hi

Update. I am still having issues got automation ti work but every time a criteria cell from a reference sheet is made it adds a new line rather than editing old line. I think it might be best to start from scratch and get advice on how to build from expert users.

I have sheets kept up to date by 3rd parties. Best to think about in terms of suppliers. I want to collate any orders destined for us in a sheet. I need to pull in several but not all of their columns. I need to know as new orders are added to any of their sheets as a new line in ours or if any of the information changes such expected delivery date, consignment size or distribution centre(not coming to us). That changed info needs to overwrite the existing info not create a new line. I then need to display a gantt with expected delivery time to our new shipment date. It would be good to have a spark line on this for an interim date if anyone knows how to do that on gnatt?

An aside, when using automated workflow I get an error if I have a modified timestamp system column this should populate when new rows added/changed but. Even when renamed the automation thinks there are duplication in system cols and won't run. Error: system column appears in source and destination sheets.

Thanks in advance

Genevieve P.

Hi @TruCal

Currently the Copy Row workflow will copy over an entire row from one sheet to another, as you've found, and you are not able to select just one cell or one column to copy over.

Based on the most recent description of your situation, I would actually recommend creating a Report from your source sheet.

A Report would enable you to only bring in the columns that you need to see, and you can use a Filter to only show the rows that are relevant to you. As new rows are added to that source sheet, the new row will appear in your Report.

Reports are like windows into the underlying sheet, so this means that as the rows in the sheet are updated, that new information will automatically appear in your Report.

Here's a free webinar that you may find helpful: SmartStart: Reporting

Cheers,

Genevieve

TruCal

Thanks

There are additional columns we add in the sheet calculated from the info in the other sheets and brought through. Can reports be flexible to track that info or would we need an additional sheet for that info and have all calculated columns in report? Can e pull back the calculation back to sheet so we can edit "our own" existing sheet?

Genevieve P.

Hi @TruCal

Thank you for clarifying; no, you won't be able to add in additional columns to the Report that don't exist in the underlying sheet.

Depending on how large your source sheet is, you could add these columns to the Sheet then hide them in the sheet and only show them in the Report.

If your sheet is too large to add in these extra columns, then you could use an INDEX(MATCH formula to pull data from one sheet to another, but in this instance you would need to have the matching ID in both sheets before the formula can calculate.

It actually sounds like the best solution would be to use the add-on called DataMesh. This can bring data from one sheet into another (including bringing in the unique ID to match across sheets) and it can auto-create cell-links between the sheets so that your data will stay up to date. I would suggest talking to your System Admin to see if your plan has already purchased DataMesh, and see this article for more information: DataMesh

Cheers,

Genevieve

TruCal

Does data mesh support multiple source single target?

Genevieve P.

Hi @TruCal

Yes, you can select a Report as the source (with multiple sheets in the Report). See: Combine or Refine Data Sets with DataMesh

Cheers!

Genevieve