Cell Change Alert

Vince Stamey

Hi,

Does anyone know if there is a way to send an alert if the value in a cell changes by more than a certain percentage? So, if I have 100 in a cell (text/number) and the value changes by over 5%, I'd like to be able to send an alert.

Thanks,

Vince

KPH

If you are happy with that, I think that is the way to go. You don't need the helper checkbox anymore as your logic is a very simple trigger if anything in that column changes then copy the row. And as the only row with anything on is the number that you want (and it isn't a parent 😏) then you should be good!

KPH

Hi @Vince Stamey

There isn't a standard automation to do that but you could probably make one by combining a couple. My immediate thought is an automation to copy the row as it is created. In the sheet it is copied to, a column with a column formula to calculate the value plus the threshold. That gives you a static copy of your data at creation and the point to trigger the alert. Then another column with a formula that dynamically references the column in the other sheet (INDEX MATCH or similar). This relies on there being something unique in each entry, that you can match on, maybe a row ID. Then a second automation that is triggered if the value in the references column exceeds the trigger threshold.

Vince Stamey

Hi KPH,

Thanks for the response. It's one cell that changes, not a new row being added. In the image below you can see the Total Hours Forecast (4,400). It is updated when the hours worked for one of the months changes. That's the number I'm trying to alert on when it changes by over 5%.

KPH

You need a static copy of the original value somewhere, that you can then compare to the new value, so by copying the row as it is added you create your reference point. If you are not adding the row but calculating it based on other rows you can instead trigger the automation to create a copy each time it changes. If you only want to monitor changes in one cell within a column you can use a condition to only copy if that is the row that changes, and this will make it even easier to match. The rest of the process would be the same. I'll make a couple of sheets and take some screen shots.

Vince Stamey

Thanks! Looking forward to seeing it.

Vince

KPH

Let's pretend this is your sheet.

The yellow cell is populated based on the sum of the green cells (or however you want).

Step 1 - I have added a column here with a tick next to the yellow cell to identify it.

Step 2 - I then create an automation work flow like this:

The workflow is triggered whenever there is a change in the Total Hours Forecast Only column.

If the Alert has a checked checkbox, the row is copied to another sheet, creating a static value of the data.

The second sheet looks like this:

The white and yellow columns are the copied columns.

Step 3 - The pink and orange columns are columns I have added with column formulas that are automatically applied to each new row.

Alert threshold is this formula:

=[Total Hours Forecast Only]@row * 1.05

It calculates the point at which to alert. So in row 1 the Forecast was 210, so 210 + 5% is 220.5. This is the point you would want an alert to be sent.

The Alert column includes this formula:

=IF(ISBLANK([Total Hours Forecast Only]@row), "", IF(INDEX({Dynamic Forecast}, MATCH(true, {Checkbox}, 0)) > [Alert threshold]@row, 1))

This checks if the Forecast is blank and if so does nothing (this is a safety just in case of blank rows).

If there is data, then it does an INDEX MATCH look up on the original sheet. It INDEXes the column where our original yellow box is, using a cross sheet reference (let me know if you need help setting that up - you can't just copy mine), then MATCHes on the row that is checked. If the value of that cell is greater than the value in the Alert threshold, it checks the box.

In my example the yellow box was originally 210 and that was copied to the new sheet. Then I changed some green cells and it changed to 1700. At this point two things happened. The Alert checkbox in row 1 of the second sheet was checked and the new figure of 1700 was copied into a new row.

If I change the green boxes again, like this:

The second sheet updates like this:

Now the Alert box in the second row is checked and a new base threshold is entered.

If I make another change and the forecast decreases, it is copied but the alert checkbox does not change:

Increase but under threshold, and it is added, and not checked:

Increase again, and over threshold, and it is added, and checked. At this point 2 rows are checked as the new value is more than 5% above 2 previous base values. You might want to add another step to avoid a double alert. If so let me know.

Step 4 - Sent your alert whenever that Alert box is checked using a second automation:

Step 5 - Bonus step! If you add a date column to the second sheet you can record the date that the alert was sent, should you ever want to know when the most recent alert was sent, or how many alerts are sent per month.

Does that sound like it would do what you need?

Vince Stamey

Hi KPH,

This might work. I'll get it setup and let you know how it goes. Thank you!

Vince

KPH

Great. One thing I was going to mention but forgot by the time I had taken all the images, was that you can skip the pink column if you want to and just use this formula in the orange one.

=IF(ISBLANK([Total Hours Forecast Only]@row), "", IF(INDEX({Dynamic Forecast}, MATCH(true, {Checkbox}, 0)) > ([Total Hours Forecast Only]@row * 1.05), 1))

The part in bold is what was in the pink column. If you aren't interested in seeing it, you don't need to.

I look forward to hearing that you were successful!

Vince Stamey

Hi KPH,

It's copying to whole 1st sheet over, instead of just the row that changed. Any ideas?

Here's the automation:

Here's the 1st sheet:

Here's what is copied to the 2nd sheet (I have the formula you suggested open to see):

KPH

Odd. The extra rows won't be a problem but I am a little concerned the alert helper is checked on row 1 (ignore that - I was looking at the wrong column!). Can you delete all the extra rows for the time being and make some changes to the master sheet? See what happens?

Vince Stamey

Deleted everything except this:

It copied this:

KPH

It isn't terrible but I don't like it. Without digging into the whole sheet, I think the easiest thing is to create a dedicated column.

Can you add a new column to your first sheet with a simple formula to be equal to the cell we are concerned about (my yellow cell). Then lock the column so there is nothing else in there. Then change your automation to use the new column instead of Total Hours Forecast Only. That way, it should only ever trigger and copy that specific row as there is nothing else in the column to change.

Vince Stamey

Added column:

Changed automation:

It still copied multiple rows:

KPH

Can you share the alert helper 2 column as it appears in the second sheet?

Vince Stamey

Here it is:

KPH

1500 was the original and 1600 was what you changed it to?

I am not sure why the extra rows are being copied. They do not for me and your automation says to copy rows if Alert 2 changes, which it hasn't for the cells with nothing in. Do double check there is nothing in those cells (no weird formula returning blanks?).

The extra rows could be harmless. Do you want to press on as you are and I will have a think over the weekend when my brain might be more fresh than it is at the end of a very long week!

You don't need the alert at the end of the copy rows automation BTW. The alert is on the second automation.