Today(-xx) function in Cross Reference sheet

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

Find more posts tagged with

Formulas

Comments

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

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.

Elizabeth Aird

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

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.

Elizabeth Aird

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

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?

Elizabeth Aird

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

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.

Elizabeth Aird

This is a challenge beyond my abilities! lol

I appreciate your help!!!

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.

Elizabeth Aird

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

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.

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.

Elizabeth Aird

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

Sign in to join the conversation!

Quick Links

Trending discussions

Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…
INDEX COLLECT MIN
Desired result is to pull the Initiative Name where the Days to Due Date is the least (so, due the soonest); then to make an index for the Top 10 Initiatives Due Soonest Currently using this formula: =INDEX(COLLECT({Project Master - Initiative}, {Project Master - Days to Due}, MIN({Project Master - Days to Due})), 1) This…