Conditional Formatting with nested IFs

I have one sheet (Sheet 1) for reporting purposes. It contains a column that uses a list with 5 status of the Charter listed.

Please Login or Register  to view this content.

The second sheet labelled "Data" contains dates for each status listed above, e.g. Draft = Jan. 1, 2011, Team Review= Jan. 5, 2011, PMO Review=Jan. 15, 2011 etc.

I have also defined the above as ranges, i.e.:

Please Login or Register  to view this content.

ANy help is appreciated cause I think I might go blind trying to figure this out.
I am trying to perform conditional formatting in Excel 2003 such that:

IF CurrentCell = "N/A" OR (CurrentCell="PMO Review" AND Today'sDate - CharterDraftDate > 15) OR (CurrentCell="Executive Review" AND CharterExecutiveReviewDate - CharterDraftDate >15) THEN CurrentCellBackground = RED

I tried using the following formula in conditional formating, but it does not work unless only the first condition is satisfied, i.e. CurrentCell="N/A":

Please Login or Register  to view this content.

Thanks for your feedback, it deifinitely helps for the pointers you gave to simplify the formula, but unfortunately it did not work for me. I am attaching the file, as maybe I did not explain things well or am interpreting the data incorrectly as stated.

One thing that was a little unclear is how do I use the same formula in subsequent cells without using INDEX. i.e. apply the same conditional formatting to check conditions of other cells in the same named range?

Appreciate your input.

Finally figured it out in a really long way, but achieved what I was looking to do. Created three separate Conditional formats to evaluate various conditions.