OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

formula to refer to current cell's neighbor

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
MrKotter
General User
General User


Joined: 02 Feb 2010
Posts: 5

PostPosted: Tue Feb 02, 2010 5:45 am    Post subject: formula to refer to current cell's neighbor Reply with quote

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
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 02, 2010 6:20 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
MrKotter
General User
General User


Joined: 02 Feb 2010
Posts: 5

PostPosted: Tue Feb 02, 2010 6:30 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 02, 2010 6:36 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
MrKotter
General User
General User


Joined: 02 Feb 2010
Posts: 5

PostPosted: Tue Feb 02, 2010 6:42 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 02, 2010 6:50 am    Post subject: Reply with quote

Conditional format of active cell A2: <Cell Value> <equals> A1 <your_style>
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
MrKotter
General User
General User


Joined: 02 Feb 2010
Posts: 5

PostPosted: Tue Feb 02, 2010 7:04 am    Post subject: Reply with quote

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()...
Back to top
View user's profile Send private message
MrKotter
General User
General User


Joined: 02 Feb 2010
Posts: 5

PostPosted: Tue Feb 02, 2010 7:11 am    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 02, 2010 7:20 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group