Formula to surface specified data and date from project task

I am trying to to surface to latest EOT from the task to row 1 on EOT Stage, and respectively the corresponding End Date to EOT End Date row 1.

We may state EOT 1 - 5 on the project sheet, but it will only take effect the date is filled in. Therefore, the latest EOT need not necessary be 5. But in the following example, I am taking EOT 5 as the latest.

Would appreciate assistance.

Best Answer

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Answer ✓

    Viviven, try these formulas :

    EOT No column:

    =IF([EOT End Date]@row <> "", INDEX(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""), MATCH([EOT End Date]@row, COLLECT([End Date]:[End Date], Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""), 0)))

    EOT End Date:

    =MAX(COLLECT([End Date]:[End Date], Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""))


    Gia Thinh Technology - Smartsheet Solution Partner.

Answers

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭

    Hi @Vivien Chong

    Try the formulas below. Hope it works for you.

    EOT No column:

    =INDEX(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""), COUNT(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> "")))

    EOT End Date:

    =INDEX(COLLECT([End Date]:[End Date], Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""), COUNT(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> "")))


    Gia Thinh Technology - Smartsheet Solution Partner.

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭

    Hi @Gia Thinh thanks for your prompt response. I copied your formula but there seem to be an error:

    I have verified that the column names are correct and I am wondering why it doesn't reflect the correct data as what you have shown.

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭

    In my formula has 4 referenced columns. Ensure you have the same column names as in the formula or you can change the formula to match with your existing column names.


    Gia Thinh Technology - Smartsheet Solution Partner.

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭

    @Gia Thinh I am having the same column name as yours

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭

    Just shared my demo sheet with you. Check it out.


    Gia Thinh Technology - Smartsheet Solution Partner.

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    edited 05/06/24

    I have looked into your issue and there are 2 things :

    1- You need to enter date into End Date to make the formula works

    2- Modify the formulas as below:

    EOT No column:

    =IF(COUNT(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> "")) <> 0, INDEX(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""), COUNT(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""))))

    EOT End Date:

    =IF(COUNT(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> "")) <> 0, INDEX(COLLECT([End Date]:[End Date], Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""), COUNT(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""))))


    Gia Thinh Technology - Smartsheet Solution Partner.

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭

    Thanks @Gia Thinh , thanks for pointing out the error. Yes, indeed one important information is that the EOT may be blank if there is no need for EOT.

    I am thinking are we able to simplify the formula to the following?

    1. EOT No: to surface the EOT No corresponding to the maximum End Date
    2. EOT End Date: to surface the maximum end date with task containing "EOT No."

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Answer ✓

    Viviven, try these formulas :

    EOT No column:

    =IF([EOT End Date]@row <> "", INDEX(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""), MATCH([EOT End Date]@row, COLLECT([End Date]:[End Date], Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""), 0)))

    EOT End Date:

    =MAX(COLLECT([End Date]:[End Date], Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""))


    Gia Thinh Technology - Smartsheet Solution Partner.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!