Alert notification for when column goes from blank to...not blank?

Jaye Casciano

Hi all,



Looking for a way to receive a notification for the first instance in which a cell goes from blank to filled. We need the date on which a designer is assigned for any given "GRS" project - but that designer may change over time. We are only concerned about the FIRST update. Any ideas on how to set up this kind of notification?



Have tried the following:

When:Designerchanges toanything

In: any row whereTactic namecontainsGRSandDesigneris blank

Email:Jaye Tatoneright away



The way I took that was that I'll be notified when any blank Designer cell gets edited to contain anything other than a blank. However, I'm not receiving any notifications at all now.



Sure, I can set up an alert to notify me anytime it changes to anything, but then we need to cross-reference the project to see if we've already noted the first date a designer was assigned.



Hope that makes sense and would appreciate any workarounds!

Robert Casper

Hi.

First it appears that the logic you are using won't trigger because the conditions are contradictory. If "Designer" changes to "anything" then it cannot be in a row where "Designer" is blank.

Try this:

1) Add a checkbox column "BlankDesigner?" with the logic statement =IF(ISBLANK([Designer]1), 1, 0). Copy the logic statement all the way down the column, making sure that the cell reference continues to increase with each row

2) Set your notification to trigger If "BlankDesigner?" changes to anything.

When the Designer field is blank, then BlankDesigner? will be unchecked

When something goes into the Designer field, BlankDesigner? will be checked. That change will trigger the notification

When something changes in the Designer field, BlankDesigner? will remain checked because the Designer field isn't blank. This way you only get the first notification.

If someone deletes the entry in the Designer field, the BlankDesigner? field will reset and you would get a notification for that.

Best of luck!

Robert

Adam Overton

Additionally, please make sure you have gone to Personal Settings, and on the Settings tab, you need "Include my changes in sheet notifications" checked if you want to test them yourself. Without this option checked, it would only notify you if somebody else made the change!

Jaye Casciano

Hi Robert, thank you kindly for your suggestion and in a perfect world I would have already used this - but the sheet I need this information from is a beast among beasts, and from experience we already know we can't trust formulas to populate when new entries are added. There are so many people in and out of this sheet, entering new rows of data manually and/or by form, rows being moved around etc. that a formula is sure to break somewhere along the line. Was hoping to find a pure Alerts-based solution but I don't think there is one!

Jaye Casciano

Many thanks Adam, I never realized this was an option. Great tip!

J. Craig Williams

Jaye,

你解决这个问题了吗?如果不使用Zapier,这s can likely be solved with a Zap, especially since this is a one-sheet functionality, not many sheets.

Craig