What is the best practice for referencing data in another sheet?

I currently have a Data Shuttle updating/replacing all cells in sheet 1.

In a second sheet I have a formula that performs an Index/Match with the result coming from that first sheet.

I noticed the cell history on the second sheet is capturing the first sheet values disappear and then come back in (example below). Assuming this is sheet 1 Data Shuttle clearing the values and replacing them. I'm worried that could be a problem if I need to structure an automation triggering on this column changing.

Is there a better way to do this? I do have DataMesh access and was considering trying that instead.

Find more posts tagged with

DataMesh

Best Practice

Data Shuttle

index/match

Accepted answers

Paul Newcome

You wouldn't be able to set up an automation triggered off of this column anyway because automations won't trigger off of cells containing cross sheet references. You would have to have a date based trigger that is set to run daily (you can set up multiple if you need more frequent) and incorporate a condition in the automation that

All comments

Paul Newcome

electrichead

Thanks for pointing that out @Paul Newcome.
I went ahead and set up a DataMesh for it.

Paul Newcome

I actually meant to include in my last comment (but got distracted) that a DataMesh would be a great way to go for this.

electrichead

I found another instance of this where I used an Index/Match.

To the earlier point Paul made, the automation can't trigger off the formula, but I had used it as a CONDITION, which tripped up my logic when if flipped from the expected state (good lookup data) to #NO MATCH.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.