Absolute reference conditional formatting
-
- Posts: 83
- Joined: Sun Jan 17, 2010 10:00 pm
Absolute reference conditional formatting
Hi,
I have this in the conditional formatting formula is box
IF(ISNUMBER(L196);L196>=LARGE(L$169:L$220;L$167))
But when I insert rows unlike the spread sheet formatting does not move the reference so it loses the formatting.
ICan someone solve this for me please because I cannot.
Thanks
Michael
I have this in the conditional formatting formula is box
IF(ISNUMBER(L196);L196>=LARGE(L$169:L$220;L$167))
But when I insert rows unlike the spread sheet formatting does not move the reference so it loses the formatting.
ICan someone solve this for me please because I cannot.
Thanks
Michael
OpenOffice 4.1.3 on MacOS X
Re: absolute reference conditional formatting
Put the calculations in a cell, then test the result from the CF
AOO4/LO5 • Linux • Fedora 23
Re: absolute reference conditional formatting
If your problem related to a bug then you need upgrade your very-very old version of the OpenOffice to the latest Still LibreOffice ot to the latest Apache OpenOffice version. Nobody will fix the bugs of the older versions.Michael
OpenOffice 3.1 on MacOS X
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
-
- Posts: 83
- Joined: Sun Jan 17, 2010 10:00 pm
Re: absolute reference conditional formatting
Hi,
I am using Openoffice 4.1.3
The formula works in CF until I insert rows then it loses the correct range which moves upward.
The absolute formules I have in the cells change when rows are inserted but the formula in CF does not.
Thanks
Michael
I am using Openoffice 4.1.3
The formula works in CF until I insert rows then it loses the correct range which moves upward.
The absolute formules I have in the cells change when rows are inserted but the formula in CF does not.
Thanks
Michael
OpenOffice 4.1.3 on MacOS X
Re: absolute reference conditional formatting
Please update your signature in this Forum.I am using Openoffice 4.1.3
And please upload an ODF type sample file here.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
-
- Posts: 83
- Joined: Sun Jan 17, 2010 10:00 pm
Re: absolute reference conditional formatting
Hi Zizi64,
I updated my profile & attached is a small sample of the problem.
Thanks
Michael
I updated my profile & attached is a small sample of the problem.
Thanks
Michael
- Attachments
-
- Conditional Format.ods
- (9.91 KiB) Downloaded 79 times
OpenOffice 4.1.3 on MacOS X
Re: absolute reference conditional formatting
Works well wth AOO 4.1.4 andLibreOffice 5.4.
Notice the "expand references" option in the options dialog (options>calc>general
It makes a difference when you insert on top and directly below the referenced range.
With this option being turned off, if the reference is C11:C26 and you insert at row 11, you move down the referenced range to C12:C27.
With this option being turned off, if the reference is C11:C26 and you insert at row 27, the reference remains the same.
With this option being turned ON, if the reference is C11:C26 and you insert at row 11, you expand the referenced range to C11:C27.
With this option being turned ON, if the reference is C11:C26 and you insert at row 11, you expand the referenced range to C11:C27.
Edit: My bad. I confused the conditionally formatted range with any reference used in the condition formula. |
It makes a difference when you insert on top and directly below the referenced range.
With this option being turned off, if the reference is C11:C26 and you insert at row 11, you move down the referenced range to C12:C27.
With this option being turned off, if the reference is C11:C26 and you insert at row 27, the reference remains the same.
With this option being turned ON, if the reference is C11:C26 and you insert at row 11, you expand the referenced range to C11:C27.
With this option being turned ON, if the reference is C11:C26 and you insert at row 11, you expand the referenced range to C11:C27.
Last edited by Villeroy on Wed Nov 15, 2017 1:17 am, edited 2 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: absolute reference conditional formatting
This is one (more) reason I try to avoid complex formulas in the conditional formatting.
But, if you still want to, and have to use older versions of Calc, here's one approach: use OFFSET to find the range of cells for LARGE. This is still subject to the option & behavior Villeroy has pointed out.
But, if you still want to, and have to use older versions of Calc, here's one approach: use OFFSET to find the range of cells for LARGE. This is still subject to the option & behavior Villeroy has pointed out.
- Attachments
-
- Conditional Format_offset.ods
- (8.68 KiB) Downloaded 84 times
AOO4/LO5 • Linux • Fedora 23
-
- Posts: 83
- Joined: Sun Jan 17, 2010 10:00 pm
Re: absolute reference conditional formatting
Thanks for your help
Maybe I asked the wrong question.
Should I have asked - How can I find and highlight the largest number in a column?
Also acknak are you saying that if I update from 4.1.3 to 4.1.4 I will not have the problem with CF ?
Thanks
Michael
Maybe I asked the wrong question.
Should I have asked - How can I find and highlight the largest number in a column?
Also acknak are you saying that if I update from 4.1.3 to 4.1.4 I will not have the problem with CF ?
Thanks
Michael
OpenOffice 4.1.3 on MacOS X
Re: absolute reference conditional formatting
Edit: My bad. I confused the conditionally formatted range with any reference used in the condition formula. |
Last edited by Villeroy on Wed Nov 15, 2017 1:16 am, edited 4 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 83
- Joined: Sun Jan 17, 2010 10:00 pm
Re: absolute reference conditional formatting
Hi Villeroy,
Which version of OO are you using?
If its 4.1.4 will it work with Mac OS X 10.9.5 Mavericks. I know I am going to have to upgrade my Mac but with Mavericks everything just works!! LoL
Thanks
Michael
Which version of OO are you using?
If its 4.1.4 will it work with Mac OS X 10.9.5 Mavericks. I know I am going to have to upgrade my Mac but with Mavericks everything just works!! LoL
Thanks
Michael
OpenOffice 4.1.3 on MacOS X
Re: absolute reference conditional formatting
STOP! Forget what I wrote yesterday. The validated range behaves normally. It expands according to the inserted rows, but not the reference within the condition formula. The bug is still in AOO, but it is not in LibreOffice.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 83
- Joined: Sun Jan 17, 2010 10:00 pm
Re: absolute reference conditional formatting
HI,
I am still playing with this and have come to a way of achieving what I want
but now I have a different problem.
When there are duplicate numbers and I ask for the the top 3 it counts the duplicates individually so in the attached example it will not show the top two numbers until I enter 4 in C9. I need it to count the 3 x 13 as 1 top number and show 10 as the second largest number.
Can this be done?
Thanks for all the help
Michael
I am still playing with this and have come to a way of achieving what I want
but now I have a different problem.
When there are duplicate numbers and I ask for the the top 3 it counts the duplicates individually so in the attached example it will not show the top two numbers until I enter 4 in C9. I need it to count the 3 x 13 as 1 top number and show 10 as the second largest number.
Can this be done?
Thanks for all the help
Michael
- Attachments
-
- Conditional Format with H.ods
- (9.66 KiB) Downloaded 84 times
OpenOffice 4.1.3 on MacOS X
Re: absolute reference conditional formatting
Nothing here specific to CF!
There is a workaround, however, but there are limitations to it: You can disambiguate equal entries by adding little "sub-significant" values based on some unambiguous deterministic generator. The attached example is demonstrating it using the ROW() function for the purpose. This is simpler and more efficient than someting based on COUNTIF() e.g.
Out of curiosity: Can you concisely explain the aspects of your task needing to apply such an uncommon ranking?
That's the way any ranking I know does. The standard functions RANK(), LARGE() and SMALL() are made for cases of standard ranking. You cannot use LARGE() or SMALL() if you actually need to count an arbitrary number N of equal values for one "rank" without omitting (N-1) rank numbers.TengoKbailar wrote:When there are duplicate numbers and I ask for the the top 3 it counts the duplicates individually ...
There is a workaround, however, but there are limitations to it: You can disambiguate equal entries by adding little "sub-significant" values based on some unambiguous deterministic generator. The attached example is demonstrating it using the ROW() function for the purpose. This is simpler and more efficient than someting based on COUNTIF() e.g.
Out of curiosity: Can you concisely explain the aspects of your task needing to apply such an uncommon ranking?
- Attachments
-
- aoo91188SkewRanking_1.ods
- (10.87 KiB) Downloaded 87 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
-
- Posts: 83
- Joined: Sun Jan 17, 2010 10:00 pm
Re: absolute reference conditional formatting
Hi Lupp,
Thanks for your answer, I will play with the example you sent.
For your curiosity
I have a list of 50 different activities and I want to see clearly which one is giving the best result or second best. At the moment I have to watch the list as I change the top chosen number, say 2 to 4, to see when the highlights change. What I really need is to show just the chosen biggest number and duplicates of same.
Ummm While I am trying to explain this I am thinking that I am going about it in the wrong way maybe it is not as easy as I first thought. LoL
Thanks again
Michael
Thanks for your answer, I will play with the example you sent.
For your curiosity
I have a list of 50 different activities and I want to see clearly which one is giving the best result or second best. At the moment I have to watch the list as I change the top chosen number, say 2 to 4, to see when the highlights change. What I really need is to show just the chosen biggest number and duplicates of same.
Ummm While I am trying to explain this I am thinking that I am going about it in the wrong way maybe it is not as easy as I first thought. LoL
Thanks again
Michael
OpenOffice 4.1.3 on MacOS X