Multiple IF statements in once cell

[mmckillen]

So I am trying to create a project timeline for a project I am managing and I want to create a simple spreadsheet with three main columns

Due Date<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Date Completed<o></o>
Duration of Task

I’ll apologize in advanced for being long winded, but it’s my experience that if you don’t give enough detail, the answers you receive can be all over the place. With that said, here’s my situation.

The due date fields are self populated based on a previously specified duration of each task (set by my team) and the project start date. The date complete is the main (and I guess the only input field), and I want this to drive other due dates. So if a task is finished one day late, any task that is associated / dependent on the late task will automatically have a changed due date.

I am good up to this point. Where the logic gets sticky is when a specific task is turned in late, but it does not affect all other tasks, just specific tasks. So essentially there are some tasks that will affect all others, and some tasks that will only affect specific tasks.

For example, Task 1 may affect tasks 2-10, Task 3 may affect tasks 4-7, and task 4 may affect tasks 5-7.

Below is a section of my spread sheet:


<TABLE style="WIDTH: 732pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=975><COLGROUP><COL style="WIDTH: 111pt; mso-width-source: userset; mso-width-alt: 5412" width=148><COL style="WIDTH: 117pt; mso-width-source: userset; mso-width-alt: 5705" width=156><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 5010" width=137><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 146pt; mso-width-source: userset; mso-width-alt: 7094" width=194><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 111pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=148>Owner (A)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 117pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=156>Task (B)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 width=95>Due Date (C)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 103pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 width=137>Actually Delivered (D)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 146pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 width=194>Cumulative Days to Complete (G)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 width=117>Days to Complete (H)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69></TD></TR><TR style="HEIGHT: 40.5pt; mso-height-source: userset" height=54><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 40.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 height=54>(3) Client</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70>Data Sheet</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>1/10/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72></TD></TR><TR style="HEIGHT: 40.5pt; mso-height-source: userset" height=54><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 40.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=54>(4) Hobsons</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>Build Application</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67>1/15/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>5</TD></TR><TR style="HEIGHT: 40.5pt; mso-height-source: userset" height=54><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 40.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 height=54>(5) Client</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70>First Review</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>1/20/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl73>10</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl73>5</TD></TR></TBODY></TABLE>
<o>
Here are my formulas. Due date is self populated for all cells using:<o></o>
<o></o>
=IF(D3="", (C5+H6), (C3+G6)+(D3-C3))<o></o>
<o></o>
So essentially this formula is telling us that all other task due dates are dependent on the completion of Data Sheet.<o></o>
<o></o>
But not all tasks affect the rest of the timeline. For example, Build Application only affects First Review. I understand the formula that needs to be placed in the Due Date cell, but because there is already a formula in there (see above formula) I don’t know how to add a second, or in some cases a third or fourth.<o></o>
<o></o>
I don’t think conditionals will work because I am checking each Due Date cell to make sure the Actually Delivered cell that drives the above due date is empty, if it’s not it needs to calculate a new due date, not jump into another IF statement.

Ahhhhh… any solutions to have multiple non conditional IF statements in one cell?! If not, any other solutions that might take care of what I need? <o></o>
<o></o>
Thanks, I know this is a lot, but I appreciate the help!

<o>P.S. - I know MS Project or a similar program would be easier, but I am confined to Excel.</o>
<o></o>
<o>Also, if there is anything that needs clarification, let me know. I will be checking back frequently. </o>
</o>