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
-
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
-
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.
-
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.
-
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.
-
@Gia Thinh I am having the same column name as yours
-
Just shared my demo sheet with you. Check it out.
Gia Thinh Technology - Smartsheet Solution Partner.
-
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.
-
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?
- EOT No: to surface the EOT No corresponding to the maximum End Date
- EOT End Date: to surface the maximum end date with task containing "EOT No."
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!