Alert Triggered by sum threshold on a specific cell

J Smith

Hello all,

I have a need to alert the person in charge of the spending when the total spending reaches a certain threshold. Actually, multiple levels would be ideal. The source sheet pulls invoice totals from several other sheets and adds them up on a monthly basis. After the calendar month is over, this sheet will recalculate the totals. So based on the total for the current month is what I need the alerts to be triggered. Circled is the cell I am hoping to get this working. Hoping there is a solution for this I am unaware of. Thanks

Paul McGuinness

Hi @J Smith

Makes sense, I would pop in a hidden column and on the same row as the This Month figure add this formula

=ROUNDDOWN(Authorizations@row / 5000) then set the notification alert to trigger only when this cell value changes.

Using rounddown should prevent the cell value increasing to 3.1, 3.2 etc. and will only change when the value increases to a multiple of 5000 i.e. $5k, $20k etc. so the cell should always read a whole number 1, 2, 3 etc.

That should allow the notification to only trigger every time the value crosses a new $5k threshold.

Think that should do the trick.

Hope this helps

Thanks

Paul

Paul McGuinness

HI @J Smith

You should be able to create a notification for this as below, leave the trigger as when any rows change and any fields change

The conditions will restrict this back to only happen on the row you want.

Set the first condition to only be where the source column says this month

and the second condition is where the authorisation value is greater than X (being whatever threshold you want)

Hope that helps

Thanks

Paul

J Smith

Hello @Paul McGuinness thank you for this. This does work but presents a small issue. Currently, I have the alerts set in $5K increments. I need the notification to trip once every 5K and not every time the total increase any amount less than the next Threshold.

Make sense?

Paul McGuinness

Hi @J Smith

Makes sense, I would pop in a hidden column and on the same row as the This Month figure add this formula

=ROUNDDOWN(Authorizations@row / 5000) then set the notification alert to trigger only when this cell value changes.

Using rounddown should prevent the cell value increasing to 3.1, 3.2 etc. and will only change when the value increases to a multiple of 5000 i.e. $5k, $20k etc. so the cell should always read a whole number 1, 2, 3 etc.

That should allow the notification to only trigger every time the value crosses a new $5k threshold.

Think that should do the trick.

Hope this helps

Thanks

Paul

J Smith

@Paul McGuinness That did the trick! Thank you so much!! Have a wonderful weekend

Paul McGuinness

Hi @J Smith glad that did it, no worries at all and have a great weekend too.