I have a table of product data that is returned from a linked Access query.
One of the columns contains a date. I create a calculated column headed
1-Jan-2005, and then 1-Feb-2005, etc all the way to now
I then try to create a simple IF statement that would compare the column
header with the date column in the table and return a Over or Not
dependending on whether the data in tha table is greater that the column
header.

=IF(Table_CBCC_Data[[#This
Row],[dateBudget]]Table_CBCC_Data[[#Headers],[01-Jan-05]],"Over","Not")

Now the formula is carried down to all the rows in the table but the
calculated result is always the value in the first cell irrespective of
whether the test is true or not in the rest of the cells. What am I missing
here? Is there a special way of generating calculated formulae in this
fashion?

Do you understand relative vs absolute references? In your formula,
the entry for the Access date cell must remain absolute across the
row. The entry for the column name (i.e. 1-jan-05) must be absolute
copying down each column.

If you are creating a formula in the first cell, then simply copying
to other cells, your answers will be off unless you set the proper
relative and absolute references. Just hit F2 in any cell to see what
cells that formula is pointing to and you will see what I mean.

I understand the references. That was not my question. The fact is that the
formula returns the negative irrespective of whether the answer was true or
not. After a bit more research it turns out the column headers - even though
they were entered as dates are treated as text so the comparison is always
going to return the same result.
Thanks for your help.


"HKaplan" wrote:

Do you understand relative vs absolute references? In your formula,
the entry for the Access date cell must remain absolute across the
row. The entry for the column name (i.e. 1-jan-05) must be absolute
copying down each column.

If you are creating a formula in the first cell, then simply copying
to other cells, your answers will be off unless you set the proper
relative and absolute references. Just hit F2 in any cell to see what
cells that formula is pointing to and you will see what I mean.