Automatically enter certain Text in a cell if Date Open > 60 days

SPRINGBOK
SPRINGBOK ✭✭✭
edited 12/09/19 in Formulas and Functions

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 :)

ss copy formulas.JPG

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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?

  • SPRINGBOK
    SPRINGBOK ✭✭✭

    #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 :(

     

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    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! wink

    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.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    #1 - not sure what you mean by "overwritten by manual entry" :/ sorry

     

    What Andree said... yes

    .

    #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... yes

    .

    #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.

  • SPRINGBOK
    SPRINGBOK ✭✭✭

    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

     

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    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.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    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.

  • Nick Burrus
    Nick Burrus ✭✭✭✭✭✭

    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.

  • David Brandt
    David Brandt ✭✭✭✭

    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.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @David Brandt

    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.

  • David Brandt
    David Brandt ✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!