Identifying tasks based on the value of preceding tasksede them

[Innoguide]

I have a project network spreadsheet shown below and in column BT I need to identify each non-critical path task (value in column BS is "N") that has a critical path task (value in column BS is "Y") as one of it's immediate predecessors. Predecessor tasks are listed with commas separating them in column G. They are also separated out into columns H through Q for another function in the spreadsheet if that helps.

Note: Task ID's can be numeric or text

Thanks in advance for any help

Excel Workbook
	B	G	BS	BT
11	Task ID	Preceding Tasks * * * * * *(comma seperated)	CP?	*
12	Start	*	Y	*
13	a	Start	Y	*
14	b	a	Y	*
15	d	b	Y	*
16	e	b	N	X
17	c	b	N	X
18	f	c	N	*
19	g	*	N	*
20	h	*	N	*
21	i	h	N	*
22	j	e,d,i,g,f	Y	*
23	k	j	Y	*
24	Finish	k	*	*
25	0	*	*	*
Sheet

 

[Innoguide]

Sorry - I'm still learning to use HTML genie

Excel Workbook
	B	G	BS	BT
11	Task ID	Preceding Tasks (comma seperated)	CP?
12	Start		Y
13	a	Start	Y
14	b	a	Y
15	d	b	Y
16	e	b	N	X
17	c	b	N	X
18	f	c	N
19	g		N
20	h		N
21	i	h	N
22	j	e,d,i,g,f	Y
23	k	j	Y
24	Finish	k
25	0
Sheet

 

[Marcelo Branco]

Maybe this array-formula in BT2

=IF($BS2="Y","",IF(ISNUMBER(IF($G2<>"",MATCH("*"&$G2&"*",IF($BS$2:$BS$13="Y",$G$2:$G$13),0),"")),"X",""))

Ctrl+Shift+Enter

copy down

M.

 

[Marcelo Branco]

I hadnt seen your last post, but i think the formula still works. Checking

M.

 

[Marcelo Branco]

Disregard

The formulas are not correct

 

[Marcelo Branco]

I think (hope ) this is correct

BT12
=IF(OR($G12="",$BS12="Y"),"",IF(MAX(IFERROR(MATCH("*"&IF($BS$12:$BS$23="Y",$B$12:$B$23)&"*",$G12,0),""))>0,"X",""))

Ctrl+Shift+Enter

copy down

M.

 

[Innoguide]

Bravo Marcelo - the second one works perfectly!

Thanks again for your help!

 

[Marcelo Branco]

Bravo Marcelo - the second one works perfectly!

Thanks again for your help!

You are welcome and tks for the feedback!

M.