Excel Table Conditional formatting not doing what I want

[ECH123]

I have two tables in Excel 2007, first table details actions, tasks etc for
different individuals stating a critical date –ie date by which their action
should be completed. Second table details months from April 2009 to May 2010,
end date by which the project should be completed.

Columns AB in this case represent Table 1, and columns C to G represent
Table 2.

A B C D E F G
1. Date Person actioned Apr-09 May-09 Jun-09 Jul-09 Aug-09
2.
3. Apr-10 PA
4. complete DS
5. Sep-09 AB
6. May-09 CD
7. complete GP
8. Oct- 09 GP
9. Sep-09 DS
10.
11. Jan-10 EH
12. complete AM
13. Dec09 JS

I have put my IF(OR) commands into Table 2 i.e to look at column A, and put
in blanks if complete, or blank etc.
My problems are as follows:
1) I initially wanted C1 to look at the date in A3, and if more than 6
months, leave blank, but I found that as I copied the formulae across and
down, my absolutes and references were ignored. So I decided to leave it as I
could not manually go over 100s of lines to make the formula look at the
correct row, and column. Any answers to this are welcome
2) I then concentrated on conditional formatting of Table 2, i.e. look at
column A, if diff between today’s date and row say A3 is <=3, or >3<=6 or >6,
to put in the various colours for the 3 conditions. These are coming out
wrong; I have reds in blank cells, ambers where they do not apply etc. Am I
missing a step?
3) I do not mind suggestions on how to rethink this as it has taken me
several days already. At this rate the project will be over before I get the
table done.
I am sorry this is so long, thought if I gave enough detail it would be
easier to help. Thanks

 

[Ronald R. Dodge, Jr.]

Did you use dollar signs within your cell references like the following:

Within C3, you would have the reference of A3 look like $A3

Why?

This way, it will always refer to column A as you would copy across (left or
right) while when you copy up or down, the row portion will adjust as
needed. In this case, this cell reference type would be known as mixed as
it's an absolute column with a relative row.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Ronald R. Dodge, Jr.]

Did you use dollar signs within your cell references like the following:

Within C3, you would have the reference of A3 look like $A3

Why?

This way, it will always refer to column A as you would copy across (left or
right) while when you copy up or down, the row portion will adjust as
needed. In this case, this cell reference type would be known as mixed as
it's an absolute column with a relative row.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[ECH123]

Yes I did, my formula is as follows
=IF(OR(Thematic!$H10="complete",Thematic!$H10=Thematic!$H10,Thematic!$H10="",Thematic!$H10="ongoing",Thematic!$H10="summer 09"),"","=$H10-m$8")

It works for the first part - absolutes and references i.e
=IF(OR(Thematic!$H10="complete",Thematic!$H10=Thematic!$H10,Thematic!$H10="",Thematic!$H10="ongoing",Thematic!$H10="summer 09"),

but for the false statement i.e. "","=$H10-m$8"), it ignores the dollar
signs down the column and accross the rows.

Life would be easier if I could have a fromula to check if the cell is a
date, and then give it an action, but I have not been able to find one.

 

[ECH123]

Yes I did, my formula is as follows
=IF(OR(Thematic!$H10="complete",Thematic!$H10=Thematic!$H10,Thematic!$H10="",Thematic!$H10="ongoing",Thematic!$H10="summer 09"),"","=$H10-m$8")

It works for the first part - absolutes and references i.e
=IF(OR(Thematic!$H10="complete",Thematic!$H10=Thematic!$H10,Thematic!$H10="",Thematic!$H10="ongoing",Thematic!$H10="summer 09"),

but for the false statement i.e. "","=$H10-m$8"), it ignores the dollar
signs down the column and accross the rows.

Life would be easier if I could have a fromula to check if the cell is a
date, and then give it an action, but I have not been able to find one.