nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Update value in cell with value from another cell within the context of automation action 'change value in cell'

PeggyLang

@Paul Newcome

I have 2 worksheets;

Master List of site locations
Tracker
- This worksheet pulls in some data from the Master List, i.e., site name, site address, site contact, etc. using and INDEX/MATCH with the 'Site #' as the unique identifier

When the Site Address updates I want an alert to 'send to everyone shared to this sheet' with the updated address. At the moment this alert is sending numerous times due to the 'known quick in Smartsheet that stems from how cross-sheet formulas like INDEX/MATCH interact with automated triggers'.

SO . . . I added an AI suggested solution;

Problem is 'Action 2' DOES NOT result in the cell 'Last Notified Address' being populated with the ACTUAL address.

Instead screenshot below illustrates how that column is being populated.

What I need is for the column 'Last Notified Address' to populate with the last recorded address, so that when the address changes in column 'Address' I can compare with a formula to determine if it different.

Find more posts tagged with

Formulas

Automations

Accepted answers

All comments

Paul Newcome

Unfortunately, automations can't output dynamic data even using a {{placeholder}}. This is one of those instances where AI got it wrong.

Can you provide a screenshot of the sheet with the other columns as well? I'm not sure I follow exactly what you have and what you need. The way I understand it is that you have an [Address] column populated via INDEX/MATCH with cross sheet references. You want to send an alert whenever this address changes, but you are getting a bunch of false positives be cause of a 'known quick in Smartsheet that stems from how cross-sheet formulas like INDEX/MATCH interact with automated triggers'.

What I am having trouble following is this "known quirk". Smartsheet themselves say that automations can't be triggered by cells containing cross sheet references, so I would also be curious to see the trigger column and the automation's trigger.

PeggyLang

Trigger in the tracker sheet (target sheet)

Data in tracker (target sheet) for 'Address' column is INDEX/MATCH

Cell history of Address in tracker (target sheet) for a specific row that has triggered numerous times in the past day;

Cell history of Address in Master List (source sheet).

The address has not been updated since 07/09/26.

The updates noted in cell history of tracker (target sheet) were not driven because the source data changed, they were driven because the collection method is INDEX/MATCH is refreshing and temporarily registers '#NO MATCH' then as some point (I imagine when either the target sheet is accessed by someone or some other change takes place in the sheet) the cell refreshes and triggers the automation.

Paul Newcome

Is your master store list updated via data shuttle or something similar?

PeggyLang

Master Store list is manually updated

Paul Newcome

Do you copy/paste from a file or is it literally just manually updating the address column one at a time? Can you walk me through the exact update process?

I ask because the cell history is indicative of something like Data Shuttle replacing the data in a sheet with the data from a file on a regular basis. That would explain the NO MATCH when Data Shuttle clears out old rows and then the address coming back when Data Shuttle repopulates the data.

If you are not using Data Shuttle and manually updating the data, something seems a little off. Depending on the setup, I wouldn't consider it a "known quirk" but more likely a bug.

PeggyLang

There is no Data Shuttle going from Master List to Tracker.
Address is updated in Master List manually.
Address us updated in Tracker via INDEX/MATCH formula.

Paul Newcome

@PeggyLang Neither of those two recent cell history screenshots show anything happening on 7/9.

I want to make sure I am not getting confused.

What is the full cell history for the cell in the [Store #] column on the target sheet in the same row as this screenshot, and does this screenshot show the full cell history?

PeggyLang

All screenshots provide the full cell history.
What I am noticing with the Cell History of the Address from the tracker (target worksheet) is outlined below the screenshot.

07/15/26 10:45 am corresponds to input of Store # in TRACKER (target worksheet); causing the INDEX/MATCH to populate the address in tracker from master list.

09/10/26 2:05 pm corresponds to update of Store # in MASTER (source worksheet); causing the INDEX/MATCH to return '#NO MATCH'.

09/10/26 2:12 pm corresponds to updating of Store # in TRACKER (target worksheet) from 'TBD64' to '0925'; causing the INDEX/MATCH to return the address.

09/10/26 3:48 pm there were no changes to either the MASTER LIST (source worksheet) OR the TRACKER (target worksheet) at this time; it would appear from the Activity Log that all the INDEX/MATCH formulas returned '#NO MATCH'. Coincidentally(?) The 2 items I have highlighted in screenshot below are INDEX/MATCH column formulas wrapped in 'IFERROR(… "")

09/11/26 7:44 am there were no change to either the MASTER LIST (source worksheet) OR the TRACKER (target worksheet) at this time; it would appear from the Activity Log that all the INDEX/MATCH formulas returned the actual data from the MASTER LIST (source worksheet)

Paul Newcome

At this point I am going to suggest reaching out to support. This is not expected behavior and does not indicate any kind of "quirk" with automations. This is something on the back-end that went wrong.

PeggyLang

@Paul Newcome
Hey Paul, I'm still struggling with this.
I know that Smartsheet says ' automations can't be triggered by cells containing cross sheet references' but every time the cell in Target sheet (this is where the INDEX/MATCH formula is) changes, the automation is triggered.

I'm thinking of adding a step for the Project Manager (I really wish I didn't have to, as it would be one more thing they need to remember to do) which would be to 'check' a column in the Master List (source worksheet) that says 'send address change alert'. In this way when/if they ACTUALLY update the address they check the column and the formula in the target sheet looks for the check before it sends out the alert notification.

Paul Newcome

I would suggest reaching out to Support at this point. It is not functioning how they say it should function. There have been a number of known issues with Automations these past few months, so there may be something on the back-end that is broken.

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, category/bannerImageAlt, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/bannerImageAlt, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.