formula to refer to current cell's neighbor

[MrKotter]

I am trying to use conditional formatting where the condition involves the value of the cell's above cell (upstairs neighbor). Since I want to conditionally format a whole column and do not want to have to set the conditional format rule for each individual cell, I need a formula or a way to use a reference which will allow me to write the conditional format rule once (for the whole column).

To be specific, and hopefully avoid confusion, I have three columns with them being a date, a start time, and a finish time, respectively:

Feb 1 12:00 13:10
Feb 1 14:00 15:45
Feb 2 12:30 12:50
Feb 3 14:00 14:20

What I want is to have the second 'Feb 1' not be displayed (I have a Style established with font color and background color being the same). Pseudocode: if above_neighbor() equals self(), apply style.

I have searched this forum and elsewhere and I could not find any help. I was surprised because I thought this would be a common spreadsheet requirement. So, I am probably missing something right in front of my nose! Possibly conditional formatting is not the best method for this. Please pardon my rookie status....

TIA

[Villeroy]

Please look a the topic which is currently 2 positions below this one.
http://www.oooforum.org/forum/viewtopic.phtml?t=95244
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[MrKotter]

Thanks for the quick reply.

Yes, I had read that thread prior to writing this one. Unfortunately, being a rookie, that was over my head.

Do you know if there is a formula or a way to write a reference that refers to a neighboring cell? With something like that I could just enter it into the conditional formatting field.

[Villeroy]

Relative referencing is the default mode in all the spreadsheets since 3 decades. Get some book on spreadsheets (any Excel book will do).
A forum can not serve as a online distant course. You've got to know a little something.
[Tutorial] Absolute, relative and mixed references
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[MrKotter]

Hmmm... Possibly my example was not very clear...

So, is the answer yes or no in regards to a formula/function that will refer to a cell's neighbor?

[Villeroy]

Conditional format of active cell A2: <Cell Value> <equals> A1 <your_style>
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[MrKotter]

Yes, that is how you do it one cell at a time, but I would like to do it for the whole column at once.

Conditional format of active cell (any active cell currently highlighted/selected): <Cell Value> <equals> <active cell's upstairs neighbor> <your_style>

The problem is that you cannot copy and paste a conditional format down a whole column of cells and maintain the proper relative referencing (as far as I know). If you can use a formula to refer to a relative cell, then you could use that in the conditional format rule applied to a whole column.

I don't mean to be annoying, but I keep asking... Is there a formula that refers to a cell's neighbor? A la NEIGHBOR_ABOVE(), NEIGHBOR_RIGHT(), ABOVE(), RIGHT()...

[MrKotter]

Ah, I got it. The conditional format was defaulting to an absolute reference when it created the rule (kind of opposite of the usual default behavior). It will copy to a whole column at once properly if you adjust that absolute reference first.

I would still be interested if there is a "neighbor" formula if anyone knows.

Thanks!

[Villeroy]

In cell B2 ...
=A1 [top-left neigbour]
=B1 [top neigbour]
=C1 [top-right neigbour]
=A2 [left neigbour]
=C3 [right neigbour]
=A3 [bottom-left neigbour]
=B3 [bottom neigbour]
=C3 [bottom-right neigbour]

Try some and copy B2 to some other place.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org