Populate cell with multiple values found in range

Question

I'm working on making a simple Gantt chart in excel. In order to calculate the time window correctly, I need to track which tasks block others, and which tasks are blocking a given task. I'd rather this be automated than having a user manually update the chart every time something gets added or changed.

Currently I have the whole project set up as a table:

[Task Code] contains the unique code/id for a given task

[Blocks] contains the task code of the task blocked by the current task. This is limited by validation: a dropdown only presents actual values found in [TaskCode]

[Blocked By] contains a comma separated list of any task that has the given row's task code listed in [Blocks].

I'd like [Blocked By] to auto-populate as users update [Blocks]. While I know a task code block multiple tasks, it seemed a little simpler to limit that to just one, but I'm open to other ideas.

I can get a count of how many times a row's [TaskCode] occurs in [Blocks] but that's not super helpful at this point (might be good for determining if I need a max() later).

For an example:

Task Code	Description	Status	Assignee	Blocks	Blocked By
A1	Build Testing	Completed	Andrew L	A3
A2	Logic Testing	In Progress	Jeremy L	A3
A3	Solution Delivery	Not Started	Cody A	B1	A1, A2
B1	User Testing	Not Started	Chantal SC, Cody A	C1	A3

Answer 1

Hi,

Cannot understand your question. Show the expected result very clearly.

Answer 2

I would try this in the top cell of your table's "Blocked By" column:

=IFERROR(TEXTJOIN(",",,FILTER([Task Code],([Blocks]=[@[Task Code]])*([Status]<>"Completed"))),"")

If you are not using a table then you will need to change to range address style, like:

=IFERROR(TEXTJOIN(",",,FILTER($A$2:$A$5,($E$2:$E$5=$A2)*($C$2:$C$5<>"Completed"))),"")

Note that A3 should not be blocked by A1 since A1 is "Completed" - or that seems logical. If you don't need that, remove this from the formula : *([Status]<>"Completed")

You could also show B1 being blocked by both A2 and A3 since A3 is blocked by A2.... I guess it is just a matter of choice in 'inheritance' logic.....