Automatically enter certain Text in a cell if Date Open > 60 days
I currently have 2 formulas that work in conjunction to look at Acct Open Date and if it's greater than 60 days it populates a column/cell with yes or no (via checkbox) then, looks to a SECOND column/cell and tells the first cell to enter the text that is in the reference cell. It works fine but it relies on me copying and pasting the Text in the reference cell down a bunch of rows ever so often. SURELY there is a better way to do this now since I set it up a few years ago. I can get the auto populate formula tool to work but my system still relies on me dragging down and copying the reference text...sigh...I attached a screen shot
Comments
-
Are any of the cells having the formula overwritten by manual entry?
Are you inserting rows into the middle (not in row 1 or creating a new row at the bottom of the sheet?
Is there any consistency with which you have to go through this process?
-
#1 - not sure what you mean by "overwritten by manual entry" sorry
#2 - we are about to change to using a Form to create each new row and I was actually wondering how that would work given that the row created via the Form has to be added to the top or bottom neither of those options will work with the autofill formulas, correct?
#3 - yes, every account that is opened goes through this process
Let me explain the end goal and maybe there's a MUCH easier way to do it. Essentially, when we add the [Date Open] date we want that to kick off the Checks/Debit Card/Online Banking columns to populate with the text "Pending"...simultaneously, we want the 2 Day/2Week/2 Mo follow up columns to respectively calculate [date open] + 2 days, [date open] + 14 days, [date open] + 60 days.
I'm the worst at explaining these details, forgive me
-
Hi,
#1 - not sure what you mean by "overwritten by manual entry" sorry
Will you change the value manually?
#2 - we are about to change to using a Form to create each new row and I was actually wondering how that would work given that the row created via the Form has to be added to the top or bottom neither of those options will work with the autofill formulas, correct?
Fortunately, no. If the two rows under or above have the formulas, it will work.
#3 - yes, every account that is opened goes through this process
Let me explain the end goal and maybe there's a MUCH easier way to do it. Essentially, when we add the [Date Open] date we want that to kick off the Checks/Debit Card/Online Banking columns to populate with the text "Pending"...simultaneously, we want the 2 Day/2Week/2 Mo follow up columns to respectively calculate [date open] + 2 days, [date open] + 14 days, [date open] + 60 days.
I'm the worst at explaining these details, forgive me
No, you're explaining it just fine. Let's see if I got it!
Hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Try something like this.
Add the first one to the cells that you want to display Pending. Add the second one to the date columns and change the +2 to + 14 and +60 as needed.
=IF(ISDATE([Date Open]@row); "Pending")
The same version but with the below changes for your and others convenience.
=IF(ISDATE([Date Open]@row), "Pending")
=IF(ISDATE([Date Open]@row); [Date Open]@row) + 2
The same version but with the below changes for your and others convenience.
=IF(ISDATE([Date Open]@row), [Date Open]@row) + 2
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
Did it work?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
#1 - not sure what you mean by "overwritten by manual entry" sorry
What Andree said...
.
#2 - we are about to change to using a Form to create each new row and I was actually wondering how that would work given that the row created via the Form has to be added to the top or bottom neither of those options will work with the autofill formulas, correct?
Also what Andree said...
.
#3 - yes, every account that is opened goes through this process
Let me explain the end goal and maybe there's a MUCH easier way to do it. Essentially, when we add the [Date Open] date we want that to kick off the Checks/Debit Card/Online Banking columns to populate with the text "Pending"...simultaneously, we want the 2 Day/2Week/2 Mo follow up columns to respectively calculate [date open] + 2 days, [date open] + 14 days, [date open] + 60 days.
I'm the worst at explaining these details, forgive me
And again... What Andree said... Haha. You're explaining just fine. I do have another question though...
.
.
Is there something that regularly happens that is causing you to have to "copying and pasting the Text in the reference cell down a bunch of rows ever so often"?
.
Switching over to forms is actually a great way to ensure consistency when it comes to autofilling just so long as additional manual edits to a row are done with caution. It is very likely that this step alone will alleviate most of your issues.
-
Brilliant! 1,000 thank yous!
Andree, your simplified formulas worked great.
Paul, you made me realize that I can use hidden fields in a Form to keep from having to drag/copy data down the sheet! Yay!
My last hurdle is this: Our Master Tracking sheet has the 3 columns with the =IF(ISDATE([Date Open]@row), [Date Open]@row) + 14 formulas. These help the account opening team bc the 3 dates for various milestones auto populates based on "Date Open" so they don't have to do it manually. These 3 columns with the dates map out to a Report I have built for our admins to track the 3 milestones and when they complete each of them (callback, thank you letter, and 2 month follow up) they need to be able to change the date to text (something like, done or complete, or something)...problem is they obvio can't change the date in the Report bc it's a formula on the Master Tracking sheet. Is there a work around for this scenario?
Many thanks again,
Shelley
-
Excellent!
Happy to help!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
A possible workaround could be to add other columns for the manually entered text and update the formulas in the date columns to show the text if it's not empty. Could also work with checkboxes instead.
Would that work?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hey there! I don't have time t odig into this too much. But, for dates I like to use formulas that let you set a criteria
example: {Date}, >TODAY(-60)
Something like:
=IF([Date Open] >TODAY(-60), "True", "False")
Dr. St Nicholas Burrus DHA, PMP
I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.
-
I am following some of these examples to create a similar formula/action. I would like to use the Smartsheet Start and End columns, which have dates (month/day/year) in them to spit out a "text" into another column for every row. I'm creating the formula in this other column.
Was thinking along these lines...
=IF(AND([Start]1@row>= DATEONLY(2020, 2, 1), [End]1@row<= DATEONLY(2020, 5, 1)), "FY2021,Q1"
... but it is not working.
What is the best/right way to do this?
Thanks in advance for the help.
-
I hope you're well and safe!
Try something like this.
=IF(AND(Start@row >= DATE(2020, 2, 1), End@row <= DATE(2020, 5, 1)), "FY2021,Q1")
Did that work/help?
I hope that helps!
Be safe and have a fantastic day!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Andree - FANTASTIC! This solves it.... Thank you!
One related question... Can I extend this same logic you shared for several date ranges with respective quarterly "text" (so that depending on the start/end date range for a given row the right quarter text appears) and separate each additional start/end/text statement with a comma like what you have shared? Or does it require changing the structure of the entire expression/statement?
This seems intuitive but may not be logical.
Thanks again.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!