Home |
Search |
Today's Posts |
|
#1
![]() |
|||
|
|||
![]()
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. |
#2
![]() |
|||
|
|||
![]()
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. |
#3
![]() |
|||
|
|||
![]()
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. |
#4
![]() |
|||
|
|||
![]()
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. |
#5
![]() |
|||
|
|||
![]()
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. |
#6
![]() |
|||
|
|||
![]()
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. |
#7
![]() |
|||
|
|||
![]()
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. |
#8
![]() |
|||
|
|||
![]()
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Conditional Format | Excel Discussion (Misc queries) | |||
Using Conditional Format | Excel Worksheet Functions | |||
Conditional Format Stumper | Excel Discussion (Misc queries) | |||
Date Format Question | Excel Discussion (Misc queries) | |||
Excel Macro Question about Conditional Formatting | New Users to Excel |