Search code examples
google-sheetsgoogle-sheets-formulagantt-chart

Dependend end-date formula for my Ganttchart-template

i wasnt sure which title i should use so i better explain...

I try to create a Ganttchart template, there are already some out there but they dont have tasks which are dependend on other tasks end-dates. so heres my link to my sheet: https://docs.google.com/spreadsheets/d/1vson1A2-ns7tLiJXbDgJdxUStBs0YYeHANhFlafHapU/edit?usp=sharing

so basically i have a list of tasks, each task has its own id in column A. the first task has a fixed start date (start date of project), all others should be using the end dates of other dependend tasks. Im linking them by putting the task-ID in column E, if there are more tasks to be dependend on i use a "," to separate and i expect the formula to choose the latest date of all dependend end-dates

i tried already hours with chatgpt to find a solution but none works, i tried to use only some cells as reference to the formula might not apply to the full sheet:

=ARRAYFORMULA(MAX(IF(ISNUMBER(FIND(SPLIT(E3, ","), A:A)), VLOOKUP(SPLIT(E3, ","), A:D, 3, FALSE), "")))

and

=MAX(IF(ISNUMBER(MATCH(FILTERXML("<t><s>" & SUBSTITUTE(E5, ",", "</s><s>") & "</s></t>", "//s"), A:A, 0)), VLOOKUP(FILTERXML("<t><s>" & SUBSTITUTE(E5, ",", "</s><s>") & "</s></t>", "//s"), A:D, 4, FALSE), 0))

anyone has some suggestions? i appreciate!

Solution

  • You can try this formula:

    =MAX(QUERY(FILTER({$A$2:$B2\$D$2:$D2};MATCH($A$2:$A2;TRANSPOSE(SPLIT(E3;","));0));"Select Col3"))

    Note: You can't use Arrayformula because it won't work with Query.

    Here's the result:

    image

    Reference: