Issue with Cell Linking Automations

Hi,

The issue I have is that one of the sheets we use is quickly gathering too many columns because we do a lot of reporting on the data and therefore need to capture information. We have a ticketing sheet through which a ticket can have 12 different statuses as it progresses, as it reaches each stage we wish to record the date that the change happens.

To try and limit the number of columns required I thought it would be good to start a new sheet, with two common columns to the first sheet, they are the Unique ID and Status columns, the Unique ID column has been filled in so that a new row is not required every time, it is just that some of them do not exist yet. The Status column takes the status from the first sheet and I have tried this using an INDEX/MATCH formula and VLOOKUP. However, when the status changes on the main sheet, the date is not being recorded in the second sheet unless the second sheet is open at that point in time.

It appears as though column linking on a cell like this does not update the field until the sheet is open and therefore does not trigger the automations in the second sheet.

I am wondering if anyone else has hit this issue before and if so, were you able to come up with a workaround for it?

Thanks,
John

Find more posts tagged with

Automations

Cell linking

Accepted answers

Corey W.

@John_Foster I have a ticketing system that needed to record the date the ticket was completed so I am sharing my process.

We wanted the date to be specific to the Status column so a modified system column wouldn't work and we didn't want anyone to be able to edit the date so a automated "record a date" wouldn't work either.

We settled on automation to copy the row to another sheet each time the status changed. In that change log sheet we have a date created system column, so that date will be the date/time when the automation ran and copied the row, which is also when the status changed.

Then we INDEX/MATCH these dates back into the ticketing sheet. We also use them to feed into metrics sheets for dashboards and such.

If you have access to Datamesh, that could also help tie your sheets together w/o using functions.

Hopefully this gives your some ideas for your use case.

All comments

Corey W.

@John_Foster I have a ticketing system that needed to record the date the ticket was completed so I am sharing my process.

Then we INDEX/MATCH these dates back into the ticketing sheet. We also use them to feed into metrics sheets for dashboards and such.

If you have access to Datamesh, that could also help tie your sheets together w/o using functions.

Hopefully this gives your some ideas for your use case.

John_Foster

Thanks Corey, this is not the option I have gone for on the example I needed, but I will be using this elsewhere.

For now I have added an automation that reviews the status at a set hour every day. and when it is a status that does not have a date set, it sets it. Once I proved this worked I duplicated the automation 23 times so it runs once an hour.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.