Formula Question - Finding Data

cthrasher97141

Hello

I have a sheet with three columns and the primary which is not used, 2 Date columns and 1 Dropdown. The Dropdown has 5 tasks in it and the date columns are for start and end date. The Task column will have one to many of the same tasks but all with different start and end dates. For each row I need to find the next start date based on the end of the task in that row. If there is no next start after the end date of the task in the row I need to report that.

Any ideas would be greatly appreciated.

KPH

Hi @cthrasher97141

You can use an INDEX COLLECT to return the lowest (MIN) date in the Start column where the Task matches that in the current row and the Start is greater than the End in the current row. This formula would do this:

=INDEX(MIN(COLLECT(Start:Start, Task:Task, Task@row , Start:Start, >End@row )), 1)

Here is an example:

Where there is no next start date, the cell is blank.

If you would like a message to appear instead of a blank cell, you can add an IF to the formula.

Hope that helps.

jmyzk_cloudsmart_jp

Hi @cthrasher97141

https://app.smartsheet.com/b/publish?EQBCT=dabbcab0bea4457a9ffde74a0ba2fda6

[Next Start Date] =IF(ISNUMBER([Next Start Date Row]@row ), INDEX([Start Date]:[Start Date], [Next Start Date Row]@row ), "—")
[Next Start Date Row] =IFERROR(INDEX(COLLECT(Row:Row, Task:Task, Task@row , Row:Row, >Row@row ), 1), "")

cthrasher97141

Worked like a charm, thank you all so much!!

KPH

Good to hear!