Selecting content from one cell over another

Hi not sure where to start with this one

I have a range of cells where the dates may change. in the summary cell i want the most recent updated date. the cells are set up as following

Tittle Submission Original Submission New Actual Submission Date

Submission date 1/1/21 1/10/21 1/9/21

If no revised "new" dates have been set the cell would be blank along with the actual submission date

So really if date only in submission original, take that date, if date in submission new take that date, if date in actual submission take that date.

Look forward to you insights.

Best Answers

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Answer ✓

    Thanks @Mike DeLuca, this helps a lot.

    While I'm sure there are others who could offer a cleaner solution to the one below, I'm confident this should work: (where <>"" is another way of saying 'is not blank')

    = IF ([Submission Actual]@row <>"", [Submission Actual]@row, IF([Submission Revised]@row <>"", [Submission Revised]@row, [Submission Original]@row))

    This is working the IF formula backwards, looking for data in 'Actual' first, then 'Revised' and if both blank, 'Original' data.

    Hopefully I've interpreted the request correctly, and hope this helps?

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Mike DeLuca
    Mike DeLuca ✭✭✭✭
    edited 07/08/22 Answer ✓

Answers

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    Hi Mike,

    Maybe the MAX Function?

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Mike DeLuca
    Mike DeLuca ✭✭✭✭

    Hi Jason, have used the Max, but on rear occasions the actual maybe less than the revised date.

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    Hi Mike,

    Am I right in understanding that the "actual date" is driving the response required?

    In other words, if the "actual date" cell has a date, the formula should choose this date regardless of any of the other dates?

    Am I also understanding that if there is no date in the "actual date" cell, to then choose the next most recent date from the other cells?

    If so, I wonder if the formula goes something like this...

    IF ([actual date cell] = "", MAX( range of related date cells), [actual date cell])

    Hope this makes sense?

    Looking forward to further clarification otherwise.

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Mike DeLuca
    Mike DeLuca ✭✭✭✭

    HI @Jason Albrecht

    A better description would be.

    If date in submission original this goes to = submission latest cell

    If date in submission original & Submission revised, select submission revised = submission latest cell

    If date in submission original & Submission revised & submission actual, select submission revised = submission latest cell.

    The issues is sometimes the submission revised date may actually be less that the submission original. this is why the Max formulate is not always going to work for me.

    Hopefully this is clearer than my first descritpion.

    Regards Mike



  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Answer ✓

    Thanks @Mike DeLuca, this helps a lot.

    While I'm sure there are others who could offer a cleaner solution to the one below, I'm confident this should work: (where <>"" is another way of saying 'is not blank')

    = IF ([Submission Actual]@row <>"", [Submission Actual]@row, IF([Submission Revised]@row <>"", [Submission Revised]@row, [Submission Original]@row))

    This is working the IF formula backwards, looking for data in 'Actual' first, then 'Revised' and if both blank, 'Original' data.

    Hopefully I've interpreted the request correctly, and hope this helps?

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Mike DeLuca
    Mike DeLuca ✭✭✭✭

    Thanks @Jason Albrecht Seems to work a treat

  • Mike DeLuca
    Mike DeLuca ✭✭✭✭
    edited 07/08/22 Answer ✓

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!