Returning Latest Completed Task to Tracking Sheet

BradM

I have 2 sheets. One is a master tracker with a cell that is filled in from the schedule completed task. We have several tasks in the schedule that we want to roll into the master tracker. These tasks are not contiguous in the schedule so I was working on using INDEX MATCH with COLLECT to report the level of completion for the tasks.

I need help figuring out how to report the latest completed task. They are listed in order first to last, just separated by other tasks:

If Land is reserved, and Site was confirmed, Land reserved would have the most recent date as one cannot happen without the previous in the list completed. But how do I report that back to the master tracker? I know how to do cross sheet formulas but at the moment, I cannot figure out how to roll up the status in sheet let alone trying this cross sheet. If none of the tasks were Complete, then the cell value should be null.

Building out the logic, I figured out how to return the Completed tasks and the task with the MAX Date. Getting both together wasn't working out so well. I was ignoring the INDEX MATCH part of the equation to prototype.

Completed task(s): =JOIN(COLLECT([Task Name]:[Task Name], Status:Status, @cell = "Complete"), ", ")
Task(s) with the latest Finish Date: =JOIN(COLLECT([Task Name]:[Task Name], Finish:Finish, =MAX(Finish1:Finish15)), ", ")

I was thinking some sort of JOIN? COLLECT ([Task Name]:[Task Name], (INDEX [Task Name]:[Task Name], (MATCH "Site Confirmed by Market Team",[Task Name]:[Task Name], 0)), Status:Status = "Complete")), INDEX([Task Name]:[Task Name], (MATCH "In Platform Land Reserved",[Task Name]:[Task Name], 0)), Status:Status = "Complete")), ...

I was looking at RIGHT, FIND, etc. but none seemed to be the solution I need.

Trying it in sheet was not going well and I thought to reach out to the group. Maybe I just need a different perspective?

Thanks,

-Brad

Philip Robbins

Hi @BradM,

I would have a helper checkbox column to flag which row corresponds to the most recently completed task and use something along the lines of =IF(Finish@row=MAX(COLLECT(Finish:Finish,Status:Status,"Complete")),1,0)

Philip Robbins

@BradM I don't think you need CONTAINS or HAS in this case. I think your h1@row="A" is closer to the mark. Put a grouping value of A in for those you want to pull the max date from and go with:

=IF(Finish@row=MAX(COLLECT(Finish:Finish,Status:Status,"Complete",[h1]:[h1],"A")),1,0)

Philip Robbins

Hi @BradM,

I would have a helper checkbox column to flag which row corresponds to the most recently completed task and use something along the lines of =IF(Finish@row=MAX(COLLECT(Finish:Finish,Status:Status,"Complete")),1,0)

BradM

@Philip Robbins , That makes sense. I didn't even think of a helper column tied to specific rows. That gets around the added rows that might be added in the schedule and makes the connection to the master tracker easier.

Building the project specific schedule is simply a copy of the schedule template and tying it to the master.

Thanks!

BradM

@Philip Robbins, Still working through the problem. The helper column seems to be a good solution but the formula you suggested, =IF(Finish@row=MAX(COLLECT(Finish:Finish,Status:Status,"Complete")),1,0) does not give the desired results.

In the example below, the yellow highlighted cells are the ones that refer to the tracked tasks to report on the master tracker. The red underlined checkbox would be the correct answer to report. However, the formula suggested looks at the entire Finish and Status columns and if there is another Complete status with a later date, and error results.

How would I create the formula to look at the desired rows instead of the entire column? Is this some sort of INDEX MATCH or ?

Philip Robbins

@BradM we would just need to add an additional condition to the COLLECT function, but it's not clear to me what rule exists to determine if a column should be included in the lookup (other than you've highlighted the cells manually). If there is no programmatic rule you could just have another helper column that's a checkbox and flag these rows. Then the formula would become: =IF(Finish@row=MAX(COLLECT(Finish:Finish,Status:Status,"Complete",Helper:Helper,1)),1,0)

BradM

@Philip Robbins The list of rows to check are the ones highlighted. So, in this case, the relevant rows would be:

Site Confirmed by Market Team
Prospective Sites Confirmed
In Platform Land Reserved
Out of Platform Land - LOI sent
Lease Option Executed
Lease or Land Agreement Executed

Hence, the INDEX MATCH idea to get the relevant rows. Or could we have another column with a helper cell on each row to use as the criteria? I'm not sure.

Philip Robbins

@BradM As you cannot do a lookup on cell formatting (i.e. highlighted cells) you would need a way of identifying them that can be looked up. My recommendation of using a helper column to flag them still stands.

Based on the snip you've shared, you could look for only child rows and only those that don't contain "task" under Task Name, but without seeing the whole sheet I wouldn't recommend it.

BradM

@Philip Robbins I think the highlight is getting confusing. I was only using it to illustrate which rows work together. Could CONTAINS work? I could create a helper cell that would list the values that should be grouped.

For example, have a h1 column with the text of the grouped tasks: "Site Confirmed by Market Team, Prospective Sites Confirmed, In Platform Land Reserved, Out of Platform Land - LOI sent, Lease Option Executed, Lease or Land Agreement Executed"

Then, use something like =IF (CONTAINS ([Task Name]@row, h1@row), true, false) ?

When I try this I get the unpassable error. I tried flipping the formula to IF (CONTAINS (h1@row, [Task Name]@row), true, false) but get the same error.

I could also just put a 'grouping' value such as "A" and set the criteria to be something like if h1@row= "A" for each row that I want grouped together?

I feel like I'm close but cannot get over the hump with CONTAINS or maybe the function HAS?

Philip Robbins

@BradM I don't think you need CONTAINS or HAS in this case. I think your h1@row="A" is closer to the mark. Put a grouping value of A in for those you want to pull the max date from and go with:

=IF(Finish@row=MAX(COLLECT(Finish:Finish,Status:Status,"Complete",[h1]:[h1],"A")),1,0)

BradM

@Philip Robbins That worked. I was able to test it with other grouping values such as using "B" as the group value instead of "A" and it worked:

=IF(Finish@row=MAX(COLLECT(Finish:Finish,Status:Status,"Complete",[h1]:[h1],"B")),1,0)

It was a little bit more effort to create the helper columns and it might be an effort to maintain if the schedules change but I can tackle that later.

Thank you for all the help.

Philip Robbins

@BradM glad to hear you got it working. I'm building stuff like this every day, so if you want me to quickly look it over and check it's done in the most efficient, scalable way, drop me an email at phil@prodactive.co.uk.