Locking a cell after running formula or deleting a formula after it has run
Hello all.
I have a bit of an issue, I wrote a formula to enter todays date after a checkbox has been checked in a cell. This works great, however if I uncheck the box the date disappears.
I need the formula driven date cell to lock or hardcode the date after the formula has run once, automatically.
Current formula:
=IF(Delivered@row = true, TODAY(), "")
Someone accidentally unchecked a bunch of cells and now my dates are lost.
Thank you
Best Answer
-
@Paul Newcome Thanks Paul. I would just like to ask what you meant by a "copy row" automation? I was just thinking of a simple trigger when the checkbox is checked to update the desired date cell within the same row. ( I didn't have a date field available when I grabbed this snipit, but you just need one defined to pull it in.) Thanks for clarifiying.
Answers
-
I suggest using an automation instead. When the checkbox cell is check, then Record a date in the appropriate cell. Once it is done the date shouldn't change even if the checkbox gets unchecked and you do not need to lock the date cell
-
I agree with @Cathy Salscheider. A copy row automation is the way to go. It outputs a static date that won't change if the box becomes unchecked, and another benefit to it being a static date is that it won't change on a daily basis either. The TODAY function outputs today's date. Even if no one unchecks the box, the date will change to "today's date" every day for as long as the box is checked.
-
@Paul Newcome Thanks Paul. I would just like to ask what you meant by a "copy row" automation? I was just thinking of a simple trigger when the checkbox is checked to update the desired date cell within the same row. ( I didn't have a date field available when I grabbed this snipit, but you just need one defined to pull it in.) Thanks for clarifiying.
-
@Paul Newcome I was not aware the TODAY function would update every day automatically, that must be what happened and I assumed the person I am collaborating with accidentally unchecked the boxes. If you could kindly elaborate on the "copy row" automation I would appreciate it.
@Cathy Salscheider This works perfect so far, thank you!
-
I meant a Record a Date automation. Sometimes my fingers and brain aren't always connected. Sorry about that. I had just finished typing out an email that referred to a copy row automation and must have had a touch of auto-pilot going.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I need to update a value in a new column. How would I look at a date column that shows dates between 01/01/2025 to 03/31/2025 and place the value of Q1_2025 into another cell?
- Hi, I'm looking to write a formula using references to a rate sheet (screenshot attached with sensitive information redacted). We have several types of rates here, and also ones that are per word, whi…
- Is anyone able to help with the formula to Index Match the SO Number column and return the lowest date listed in the "finished date" column into the "first date" column? I can make it work when select…