Track task dependencies in Excel

Question

I have two tabs in my excel sheet.

Tab 1: Contains a list of all the dependencies across multiple functional area. The columns are functional area, dependency description, dependency id, plan end date, target end date.

Tab 2: Contains a list of tasks with the following columns:

Functional area, Deliverable name, deliverable description, Target Date, projected date, Dependencies ID

E.g.

Functional Area Dependencies  Plan end date  Target End date
   F1            D1            8/22           8/22
   F1            D2            8/23           8/23
   F2            D3            8/24           8/24
   F2            D4            8/25           8/25

Functional Area Deliverable Dependencies Target Date  Projected Date
   F1                de1       D1, D2        8/20           
   F1                de2       D1            8/20           
   F2                de3       D3, D4        8/20           
   F2                de4       D3            8/20           

The Dependencies ID will contain all the IDs tied to a given deliverable from tab 1. The help I want is a way to find out that if any of my dependencies tied to a given deliverable have a date past the targeted date of my deliverable, my projected date should be populated to the latest date among all the dependencies. So in the above example, projected date for de1 should be populated to 8/23 and projected date for d2 should be 8/22. Please advise. Thank you.

Answer 1

You need three pieces to get this to work. the first is this formula (we're starting work on Tab2):

=TRIM(SUBSTITUTE(MID(SUBSTITUTE("," & C2&REPT(" ",6),",",REPT(",",255)),1*255,255),",",""))

Cell C2 contains the list of dependencies, e.g. D1, D2

Notice the 1*255 bit in the formula - if you change the 1 to a 2, then this formula gives you the second item in the dependency list, then the third, etc.

Now we have three formulas that will give you the first, second, or third dependency:

=TRIM(SUBSTITUTE(MID(SUBSTITUTE("," & C2&REPT(" ",6),",",REPT(",",255)),1*255,255),",",""))

=TRIM(SUBSTITUTE(MID(SUBSTITUTE("," & C2&REPT(" ",6),",",REPT(",",255)),2*255,255),",",""))

=TRIM(SUBSTITUTE(MID(SUBSTITUTE("," & C2&REPT(" ",6),",",REPT(",",255)),3*255,255),",",""))

We're going to take each formula and wrap them up in a vlookup - this is going to look up the target end date from Tab1:

=VLOOKUP(TRIM(SUBSTITUTE(MID(SUBSTITUTE("," & C2&REPT(" ",6),",",REPT(",",255)),1*255,255),",","")),Tab1!$B$2:$D$100,3,FALSE)

In this formula I'm assuming the data to be looked up is in Tab1!$B$2:$D$100, and I'm assuming the target end date is in the third column. Note this also assumes that the dependency column is column B.

This formula as is will give me the target end date for the first dependency.

We're almost ready to wrap these up into a single function, but before we do, we want to guard against vlookup errors:

=IFERROR(VLOOKUP(TRIM(SUBSTITUTE(MID(SUBSTITUTE("," & C2&REPT(" ",6),",",REPT(",",255)),1*255,255),",","")),'Tab1'!$B$2:$D$100,3,FALSE),0)

Now we can combine all three using the Max function, which will give us the biggest (latest) date:

=MAX(IFERROR(VLOOKUP(TRIM(SUBSTITUTE(MID(SUBSTITUTE("," & C2&REPT(" ",6),",",REPT(",",255)),1*255,255),",","")),'Tab1'!$B$2:$D$100,3,FALSE),0),IFERROR(VLOOKUP(TRIM(SUBSTITUTE(MID(SUBSTITUTE("," & C2&REPT(" ",6),",",REPT(",",255)),2*255,255),",","")),'Tab1'!$B$2:$D$100,3,FALSE),0),IFERROR(VLOOKUP(TRIM(SUBSTITUTE(MID(SUBSTITUTE("," & C2&REPT(" ",6),",",REPT(",",255)),3*255,255),",","")),'Tab1'!$B$2:$D$100,3,FALSE),0))