Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Summary formula

Hey I'm trying to do this in a Sheet Summary:

  • If [Date] is today, then show me the value in the same row in column G.

This works for an individual row (1)

=IF([Date]1 = TODAY(), [G]1, 0)

But I can't get it to work with @row to search all the way down the sheet to look for the current date, I've tried these:

=IF([Date]@row = TODAY(), [G]@row, 0) (unparseable)

=IF([Date]@cell = TODAY(), [G]@cell, 0) (unparseable)

=IF([Date]:[Date] = TODAY(), [G]:[G], 0) (invalid operation)

Also I saw some posts that TODAY doesn't work unless you open/edit the sheet. Is this correct and is it true when used in the Summary fields?

Answers

  • Community Champion
    edited 07/10/24

    Hi

    I would recommend an Index(Match()) type formula.

    =INDEX(G:G, MATCH(TODAY(), Date:Date, 0))

    Here is a picture of an example working - today is 10th July.

    If there's more than 1 row with todays date in it, it would return the first match only. There are other functions that could COLLECT all the correct G values and JOIN them together in a list (I have capitalised Join and Collect as they are the function names that you would need to use).

    I hope this helps.

    Kind regards

    Debbie

    Debbie Sawyer

    CSSO (Chief Smartsheet Solutions Officer)

    Smarter Business Processes

    Smartsheet Community Champion & Ambassador

  • Community Champion

    For the refreshing issues, I usually have a checkbox column hidden on the sheet called something like "Daily"

    You can have a workflow that checks the column on a daily basis and if Daily is set to 1 then change the cell to 0 and if it is set to 0 then change it to 1.

    This means that every day the system updates all the rows on your sheet to have either a checked box or an empty box and it forces the refresh on the today function.

    You can leave this hidden on the sheet, it runs while you are off line and the sheet remains in sync with todays date.

    Kind regards

    Debbie

  • Thank you! The INDEX function worked perfectly.

    I don't have the workflow plugin so I guess will have to edit the sheet

  • Community Champion

    Ahh - are you a free collaborator? The admin on the sheet might be able to write you a workflow, or yes, just make sure you open the sheet daily! That will do it too!

  • Hi, no I am the admin on the sheet. I just thought I needed additional licenses to create workflows.

    But it looks like I can; time to dig in there I guess!

    Thanks again

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

  • I have a pretty basic sheet that I am using to develop a formula, and am encountering an issue I haven't seen before. The formula isn't including my second row for some reason. It isn't a huge issue f…
    User: "jjg279"
    Answered ✓
    9
    2
  • I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in d…
    User: "susanmgfin"
    Answered ✓
    8
    2
  • I need help to come up with the risk formula. I tried many different formulas and tried to modify it but just can't seem to have desired results. So these are the conditions I MUST meet: Program is a …
    User: "Ronak"
    Answered ✓
    29
    6