Conditional format question
 

Is it possible to write a conditional format like this?

"If the contents of this cell do not equal the contents of the last
occupied cell in this column, format the cell in a particular way."

I'm building a transaction schedule, dates in the A column, balances in
the F column. As more transactions are added to the schedule, the last
occupied cell in the column changes.

In a cell outside the schedule, I have a check value from another
source that should tie back to the last occupied cell in the row. I
want that cell to have a certain format if there's a discrepancy,
rather than me having to manually check the two figures.

Thanks in advance.

Hi!

Try this as the cf formula:

=C1=LOOKUP(9.99999999999999E+307,A:A)

Adjust the references to suit.

Biff

wrote in message
oups.com...
Is it possible to write a conditional format like this?

"If the contents of this cell do not equal the contents of the last
occupied cell in this column, format the cell in a particular way."

I'm building a transaction schedule, dates in the A column, balances in
the F column. As more transactions are added to the schedule, the last
occupied cell in the column changes.

In a cell outside the schedule, I have a check value from another
source that should tie back to the last occupied cell in the row. I
want that cell to have a certain format if there's a discrepancy,
rather than me having to manually check the two figures.

Thanks in advance.

Here is a formula

=A1<INDEX(A1:A1000,MAX((A1:A1000<"")*(ROW(A1:A10 00))))

--
HTH

Bob Phillips

wrote in message
oups.com...
Is it possible to write a conditional format like this?

"If the contents of this cell do not equal the contents of the last
occupied cell in this column, format the cell in a particular way."

I'm building a transaction schedule, dates in the A column, balances in
the F column. As more transactions are added to the schedule, the last
occupied cell in the column changes.

In a cell outside the schedule, I have a check value from another
source that should tie back to the last occupied cell in the row. I
want that cell to have a certain format if there's a discrepancy,
rather than me having to manually check the two figures.

Thanks in advance.

Hi,

There may be more elegant methods. But the following could work.
The following formula will pull out the value in the last cell of Column F,
assuming that the data start at the cell F2. Please note that the formula
will fail if there are blank rows interspersed between data in Column F.

=INDIRECT("F"&ROW($F$2)+COUNT(F:F)-1)

You can use this formula in a a helper cell to pull out the value from the
last cell of Column F, and then compare the contents of the helper cell with
the contents of the cell you want to format.

Or, you could directly embed the above formula in your conditional
formatting formula: for example, if J2 is the cell that you want CF applied
to,

=$J$2<INDIRECT("F"&ROW($F$2)+COUNT(F:F)-1)

Regards,
B. R. Ramachandran

" wrote:

Is it possible to write a conditional format like this?

"If the contents of this cell do not equal the contents of the last
occupied cell in this column, format the cell in a particular way."

I'm building a transaction schedule, dates in the A column, balances in
the F column. As more transactions are added to the schedule, the last
occupied cell in the column changes.

In a cell outside the schedule, I have a check value from another
source that should tie back to the last occupied cell in the row. I
want that cell to have a certain format if there's a discrepancy,
rather than me having to manually check the two figures.

Thanks in advance.

Note this finds the last numeric value, not necessarily the last value.

--
HTH

Bob Phillips

"Biff" wrote in message
...
Hi!

Try this as the cf formula:

=C1=LOOKUP(9.99999999999999E+307,A:A)

Adjust the references to suit.

Biff

wrote in message
oups.com...
Is it possible to write a conditional format like this?

"If the contents of this cell do not equal the contents of the last
occupied cell in this column, format the cell in a particular way."

I'm building a transaction schedule, dates in the A column, balances in
the F column. As more transactions are added to the schedule, the last
occupied cell in the column changes.

In a cell outside the schedule, I have a check value from another
source that should tie back to the last occupied cell in the row. I
want that cell to have a certain format if there's a discrepancy,
rather than me having to manually check the two figures.

Thanks in advance.

I'm assuming numeric values based on:

dates in the A column, balances in the F column.

Sound numeric to me!

Biff

"Bob Phillips" wrote in message
...
Note this finds the last numeric value, not necessarily the last value.

--
HTH

Bob Phillips

"Biff" wrote in message
...
Hi!

Try this as the cf formula:

=C1=LOOKUP(9.99999999999999E+307,A:A)

Adjust the references to suit.

Biff

wrote in message
oups.com...
Is it possible to write a conditional format like this?

"If the contents of this cell do not equal the contents of the last
occupied cell in this column, format the cell in a particular way."

I'm building a transaction schedule, dates in the A column, balances in
the F column. As more transactions are added to the schedule, the last
occupied cell in the column changes.

In a cell outside the schedule, I have a check value from another
source that should tie back to the last occupied cell in the row. I
want that cell to have a certain format if there's a discrepancy,
rather than me having to manually check the two figures.

Thanks in advance.

Maybe, but that warning is still valid so that the OP knows.

--
HTH

Bob Phillips

"Biff" wrote in message
...
I'm assuming numeric values based on:

dates in the A column, balances in the F column.

Sound numeric to me!

Biff

"Bob Phillips" wrote in message
...
Note this finds the last numeric value, not necessarily the last value.

--
HTH

Bob Phillips

"Biff" wrote in message
...
Hi!

Try this as the cf formula:

=C1=LOOKUP(9.99999999999999E+307,A:A)

Adjust the references to suit.

Biff

wrote in message
oups.com...
Is it possible to write a conditional format like this?

"If the contents of this cell do not equal the contents of the last
occupied cell in this column, format the cell in a particular way."

I'm building a transaction schedule, dates in the A column, balances
in
the F column. As more transactions are added to the schedule, the
last
occupied cell in the column changes.

In a cell outside the schedule, I have a check value from another
source that should tie back to the last occupied cell in the row. I
want that cell to have a certain format if there's a discrepancy,
rather than me having to manually check the two figures.

Thanks in advance.

B.R.'s looked easiest to adapt to my situation, and it only took me
about three minutes to get adjust the references.

Thanks to everyone for their suggestions.