Dependencies & Connections

[Pestomania]

This thread is quite a bit longer than my normal and I will do my absolute best to describe what I am trying to do. I unfortunately cannot share a link to the file, but I have uploaded everything I can think of to this thread.

Goal: Create a Critical Processes table which can identify dependencies and complete certain tasks automatically. I am also accepting suggestions for other systems that might be able to do this easier.

1. Column A: Index #
   

   The index # would be the following logic: If Parent Process, return next whole number If Sub-Process, return next decimal number of the parent process If 3rd Party, Process dependency (column C) & "-3A", "-3B", etc.

2. For all 3rd parties inputted, I'd like it to add to a table in sheet called "3rd Party Dependencies" where column A is the name of the 3rd party (Column "D")
3. Inbound / Outbound Processes will be dependent on each other in sense. Since I don't think Excel can handle that formula where If "Option 1" exists in H, then list it in G and then if "Option 1" exists in G, make sure it is in H. Maybe conditional formatting?
4. And then a Potential Revenue Impact that shows the "Parent Process" is equal to the sum of ALL processes after it. So if Process 1 is unable to produce products, we know that Process 2, 3, 4... may be impacted (therefore, Process 1 would have the sum of all processes dependent on it).

Book7
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Index #	Process Type	Dependent Process	Critical Business Process and Description	Location / Building	Shift Schedules	Inbound Processes	Outbound Processes	Headcount	Maximum Tolerable Downtime	Impact	Workaround	Recovery Strategy	Potential Revenue Impact
2	1	Parent Process		Process 1	2	1st Shift		Process 2						$ 14,000,000.00	The total possible revenue impact must be equal to the sum of all processes dependent on it (so column C?)
3	1.1	Sub-Process	1	Sub 1	1	2nd Shift	Process 1	Sub 2						$ 8,000,000.00
4	1.2	Sub-Process	1	Sub 2	1		if "Process Name" is found in column H, return all values in column D as a text join	if "Process Name" is found in column G, return all values in column D as a text join						$ 6,000,000.00
5	2	Parent Process		Process 2	2	3rd Shift	Process 2
6	2-3A	3rd Party	2	Vendor 1
7
8	The index # would be the following logic: If Parent Process, return next whole number If Sub-Process, return next decimal number of the parent process If 3rd Party, Process dependency (column C) & "-3A", "-3B", etc.)
Critical Processes

Cell Formulas
Range	Formula
N2	N2	=N3+N4
A2:A4	A2	=IF(B2="Parent Process",ROW(A1),IF(B2="Sub-Process",A1+0.1,"3rd"))

Named Ranges
Name	Refers To	Cells
Process_Index	=Table5[Index '#]	A3

Cells with Data Validation
Cell	Allow	Criteria
E2:E6	List	=Building_Name
F2:F6	List	=Schedules
G2:H3	List	=Processes
G5:H5	List	=Processes
I2	Whole number	>0
J2	List	=MTD
B2:B6	List	Parent Process, Sub-Process, 3rd Party
C2:C6	List	=Process_Index

The "Data Sheets":

Book7
	A	B	C	D	E	F	G	H	I
1	Location	Building		Schedules					Maximum Tolerable Downtimes
2	Wichita	1		1st Shift					0-4 hours
3	Wichita	2		2nd Shift					4-12 hours
4				3rd Shift					12-24 hours
5									1 day
6									2 days
7									3-4 days
8									6-10 days
9									11-15 days
10									16-25 days
11									1 month
12									2 months
13									3-5 months
14									6+ months
Data Sheets