Today(-xx) function in Cross Reference sheet

Elizabeth Aird
Elizabeth Aird ✭✭✭
edited 12/09/19 in Formulas and Functions

I am trying to make a Primary sheet for all of my escalation formulas (since i have 14 single sheets to use the TODAY() function).

 

so...

 

if(TODAY(-7)> {Date Cleaned}, "enter a name", " ")

 

so if today's date is greater than the LAST entry in Date Cleaned, I want a name to populate, otherwise "do nothing". The "Date Cleaned" is the Cross-reference sheet column name.

 

Help?

Core sheet.JPG

date cleaned column.JPG

Tags:

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    There's things missing from your description.

    In the other sheet, you need something to search for besides the date.

    You'll want to end up using INDEX(..., MATCH(...)) functionality.

    Craig

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You're cross sheet reference doesn't appear to be correct either. In Smartsheet you can't just type the column name from the other sheet inside of a set of curly brackets. The best way to reference another sheet is to use the link in the formula helper dialog box that pops up as you are entering a formula. You would then select the appropriate sheet, select the appropriate range, and (unless you change the range name) would end up with something along the lines of {Reference Sheet Name Range 1}.

     

    That is in addition to Craig's suggestion.

  • I do select the reference sheet link - and I rename the column.

     

    I do not know how to use index/match. My last Smartsheet help session - we talked through how this should work. I'll set up another session to show IT where it's not working for me.

     

    Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If you are able to provide more detail, maybe we could help you figure something out. It took me a few times using INDEX/MATCH to get the hang of it, but now I love it.

  • Ok here are some more details - ALL help is appreciated. I am using Smartsheet for Quality records. There are 12 facilities with an Equipment Sheet called "..Probe Cleaning".

    Since this is an important NEW piece of equipment, the engineers asked me to ensure that the checks are being done. SO... I am trying to automate the notifications to management. The TODAY() function means that each sheet would have to be opened and saved almost daily to get the date to update. Therefore, I created a sheet to do these calculations instead so I can open ONE sheet daily.

     

    I am trying to set notifications based on one week late, 2 weeks late and 4 weeks late. Then I will set up the email notifications.

     

    Probe Cleaning Sheet

    Date

    Select: Light to Heavy dirt for 4 probes (4 columns with drop downs)

    Operator Name

     

     

    The Cross-Reference Sheet lists all facilities and then columns for management.

    =IF(TODAY(-7) > {NGT Date Cleaned}, "John Smith")

     

    The {NGT Date Cleaned} is what I renamed the column in the original Probe Cleaning Sheet when I reference it.

     

    What I am need to do is perhaps calculate for the days since the last entry OR do the setup as seen above.

     

    I have no clue how to use index/match. I can look for the Smartsheet video - I have not done that. I have read some articles but can't seem to make the jump for what I need.

     

     

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Where are you getting the name from? That's the big question here. Is it a name from a list, the name of the last person who cleaned or checked it, is it just a manual entry on your part (if so, where is it entered/stored), is it the name listed in your Operator Name column?

  • Each weeks notification - I am entering the management name "manually" into the formula.

    The notifications will go to the Quality Manager, the Regional and Plant Manager, etc. These names are also in my contact list.

    The operator name is the one that performs the check - from a "form".

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. I think I am beginning to understand what you are looking for. Let me work on this a little bit and see what we can figure out for you.

  • This is a challenge beyond my abilities! lol

    I appreciate your help!!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    No worries. I actually had to put out a post on the community myself. I had it figured out, but when I tested it, it only worked half the time. If I made 2 identical cells and had the formula set to give the same exact result, it would work for the 1st one and not the other. If I had 10 identical cells, it would work for the first 5 and not the last 5. 20 cells = First 10 worked, second 10 didn't. Rather frustrating to say the least.

  • I don't mind sharing the sheets with you - as a viewer, if that helps.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I was able to get a response to my issue (which ended up being a user error on my end). I shouldn't need that now that I have my issue resolved, but I will keep it in mind. Depending on my workload, I am going to try to have a solution for you by end of day today.yes

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Looks like I won't be able to look at this today. I will be able to again on Tuesday. Hopefully someone else will be able to help before then though. Sorry for the delay.

  • Thank you! In the meantime, I'll keep trying some different things. Have a great weekend!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!