yogi_Apply Conditional Formatting To Project Completion Column Based On Values Of Cells In Other Columns

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #675   Aug 06, 2012     www.energyefficientbuild.com.

user Chirag Dedhia said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/sfXTL4ZCqwE)
Conditional Formatting while referring another cell
Hello everyone,
I've been using a Spreadsheet in XLSX which I uploaded recently to Google Docs and found the conditional formatting getting messed up.
What I want is that when the cell is N it should report that if the activity is overdue the background colour turns "red", while if the cell is N, but if the activity isn't overdue, then the background colour may remain "yellow". If the cell is "Y" the background colour simply turns green.
What isn't working right now in GDocs is that the cell doesn't turn red, since I'm unable to use a formula in conditional formatting dialog box which refers to another cell.
I'm aware that conditional formatting doesn't remain, while uploading a doc. But I'm also unable to create one inside of GDocs with above rules.
Please help. File attached for reference.
Regards,Chirag Dedhia
----
The link to the test file is
https://docs.google.com/

spreadsheet/ccc?key=
0AohYkIgWfY1tdDBLczloYVU1RFBDa2hSUFllYVg0SFE
----
The "overdue" cell calculates the date difference between today and "By When" the activity should have completed. And the response is calculated as "Y" or "N".
The "Completed" cell is fed by the user and a Y or N is entered, according to the activity being completed or not.
If Overdue = Y, and Completed = "N", then Completed cell background should turn red
If completed = Y, cell turns green. If completed = N and Overdue = N, the cell is orange/yellow.
In excel I was using a formula to control conditional formatting The formula is =$E3=Y, then background colour "Red"
I can't find how I can apply formulae in conditional formatting in GDocs.
------------------------------------------------------------------------------------------------
following is a solution to the problem

as of now (Aug 2012) one can not apply conditional formatting to cells of a column based on values of cells in other column(s) ... so what I did was to insert a helper column wherein I used a formula to generate Red, Green, and Orange in the cells of the helper column based on the values in cells of Completion and Overdue columns ... and then I conditionally formatted the cells of the helper columns Red, Green, and Orange per the specification of the project.