Nested COLLECT question

Options

I need help with creating the formula for the [PrevHours] column (below).

The expected logic for this formula would be: for a given row's ColValue, find the max date from all instances of ColValue, then find that row's Col2Value.

For example:

Project | Date | Hours | PrevHours

XYZ | 5/1/23 | 10 | ""

XYZ | 5/2/23 | 15 | 10

ABC | 5/1 | 100 | ""

XYZ | 6/1/23| 100 | 15

I want [PrevHours] to refer the previous latest date's entry for hours


Thank you!

Tags:

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @programd2manage

    Add a date type column 'Previous Date' with the below column formula

    =MAX(COLLECT(Date:Date, Date:Date, @cell < Date@row, Project:Project, @cell = Project@row))

    The formula for previous hours is:

    =IF([Previous Date]@row <> "", INDEX(COLLECT(Hours:Hours, Project:Project, @cell = Project@row, Date:Date, @cell = [Previous Date]@row), 1), "")

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!