Formula for Updated By and Updated Date Fields

Hi all,

Question about a formula I am trying to get started. I want to make sure this is possible before going much further. I have ONE sheet that has four different reports pulling off of it. Each report has 5 lines that Project Leads can update. We are want to have two fields (in the sheet) that autopopulate the Name of the person and the date that updates are made to ANY one of the 5 lines in ANY one of the 4 reports. I know I can use an index match formula to help me pull the name from the login email address but want to make sure the other automation is possible first.

Any advice is welcome! TIA.

Andrea

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ah. Ok. In that case you are going to want to put the formulas into sheet summary fields as opposed to directly on the sheet itself. Formulas updating will change the Modified columns and will make for some frustrating issues.


    First summary field to capture most recent date/time stamp would be:

    =MAX([Modified Date]:[Modified Date]) + ""


    Second formula to pull in the person would be

    =INDEX([Modified By]:[Modified By], MATCH([Most Recent Date]#, [Modified Date]:[Modified Date], 0))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You wouldn't reference the summary field like that. You would wrap the MAX piece in the DATEONLY function (without the plus quote quote on the end).


    =DATEONLY(MAX(..............))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    That's what I used in my formula to indicate the sheet summary field that is housing the most recent date. The one populated by the MAX statement.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

«1

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!