[Solved] MATCH function in Conditional Formatting
[Solved] MATCH function in Conditional Formatting
Hi, I'm trying to conditional format a range of cells. If you would care to download the attached document, I want to format the cells in H4:M56 only if they match any of the values in D6:d57. I can do this in MS Excel using a formula like =MATCH(H4,$D$6:$D$57,0) and it works fine, but the font and size of text is very limited with MS Excel, thus I wish to use AOO. I simply cannot seem to get the syntax correct I suspect. Thank you very much indeed for any advice or solutions.
Last edited by Twiggy on Sat Sep 16, 2017 4:49 am, edited 2 times in total.
Windows 10
Open Office 4.1.3
Open Office 4.1.3
Re: MATCH function in Conditional Formatting
ISNUMBER(MATCH(H4;$D$6:$D$57;0))
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: MATCH function in Conditional Formatting
The errors causing an unexpected behaviour were two:
-1- The formula used for the condition contained the comma as the supposed parameter delimiter. In AOO you have to use the semicolon in this place.
-2- The conditionally assigned cell style was 'Default'.
Since 'Calc' handles any numeric value different from 0 as TRUE if it is to interpret in 'logical mode', and errors are ignored by CF (just being NOT TRUE), the ISNUMBER() envelope Villeroy suggested is not strictly necessary. It helps to get things clear however.
There is, hovever another issue with CF in AOO: The relative address to use in the conditions for any single cell of the range for which CF is edited depends on the way you selected the range:
If you did it dragging with the mouse or 'Shift'+'CursorKeys' from bottom- right to top-left the "active cell" defining the relative address to use is leftmost-topmost in the range. The same "active cell" you get if you enter the range address into the 'Name Box'.
If you created the selection by a different dragging, you need to use the cell where you released the mouse botton or stopped with 'Shift'+'CursorKey', as the base for relative addressing.
-1- The formula used for the condition contained the comma as the supposed parameter delimiter. In AOO you have to use the semicolon in this place.
-2- The conditionally assigned cell style was 'Default'.
Since 'Calc' handles any numeric value different from 0 as TRUE if it is to interpret in 'logical mode', and errors are ignored by CF (just being NOT TRUE), the ISNUMBER() envelope Villeroy suggested is not strictly necessary. It helps to get things clear however.
There is, hovever another issue with CF in AOO: The relative address to use in the conditions for any single cell of the range for which CF is edited depends on the way you selected the range:
If you did it dragging with the mouse or 'Shift'+'CursorKeys' from bottom- right to top-left the "active cell" defining the relative address to use is leftmost-topmost in the range. The same "active cell" you get if you enter the range address into the 'Name Box'.
If you created the selection by a different dragging, you need to use the cell where you released the mouse botton or stopped with 'Shift'+'CursorKey', as the base for relative addressing.
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
Re: MATCH function in Conditional Formatting
This can also be done with COUNTIF($D$6:$D$57;H4)
LibreOffice 6.4.5 on Windows 10
Re: MATCH function in Conditional Formatting
Absolutely. However, both MATCH and COUNTIF may suffer from Calc's calculation options.gerard24 wrote:This can also be done with COUNTIF($D$6:$D$57;H4)
Tools>Options>Calc>Calculation...
Regular expressions in formulas = OFF (unless you really know that you want this and how to use it)
Match whole cell = depends on if you want MATCH or COUNTIF match/count a, b, c, ab or bc in abc
Both are per-document options. My personal default template has regular expressions OFF and match whole cell ON.
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: MATCH function in Conditional Formatting
That works great!! Thank you guys I'll give you all Kudos but the one that nailed it was using ISNUMBER function. My main issue was the limits using MS Excel. Can't change text size, font, colour etc. And changing : to ; would also help. One happy bunny Even saved it as ODS and opened in MS Excel and exactly what I want!
Windows 10
Open Office 4.1.3
Open Office 4.1.3
Re: (Solved) MATCH function in Conditional Formatting
When I drafted my answer I had access to an attached example 'Fox and Hounds September.ods'. Where is this attachment gone? Without it my answer may seem somehow dumb while it was very precise and thorough. I don't exactly like that.
@Twiggy edited the OQ on 2017-09-16 04:49 (forum time? CEST?) once more after the last post made here (except this one, of course). Most likely the attachment was deleted on this occasion. I don't consider this good practice.
@Twiggy edited the OQ on 2017-09-16 04:49 (forum time? CEST?) once more after the last post made here (except this one, of course). Most likely the attachment was deleted on this occasion. I don't consider this good practice.
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
Re: (Solved) MATCH function in Conditional Formatting
Lupp - I don't really see your issue with my posts. I have thanked you all. I have also not knowingly deleted the attachment.I have only edited the original post to put "[Solved] in the Subject field - which is good practice.Lupp wrote:When I drafted my answer I had access to an attached example 'Fox and Hounds September.ods'. Where is this attachment gone? Without it my answer may seem somehow dumb while it was very precise and thorough. I don't exactly like that.
@Twiggy edited the OQ on 2017-09-16 04:49 (forum time? CEST?) once more after the last post made here (except this one, of course). Most likely the attachment was deleted on this occasion. I don't consider this good practice.
Windows 10
Open Office 4.1.3
Open Office 4.1.3
Re: [Solved] MATCH function in Conditional Formatting
You and Forum Administrators/Moderators are the only people with access to your posts; I am confident that the Admins/Mods did not delete your attachment - we normal will only delete attachments for reasons of content confidentiality or (very rarely) for objectionable content. It may be that in your editing of the first post, which contained the attachment, you changed some setting which resulted in the deletion.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: [Solved] MATCH function in Conditional Formatting
There were names in the attachment. But no indication of address or location. Perhaps in hindsight I should have submitted a small, general sample of what I was trying to resolve. However, I do take your point that it was very unlikely that Admin would have deleted it. Thank you very much indeed
Windows 10
Open Office 4.1.3
Open Office 4.1.3
Re: [Solved] MATCH function in Conditional Formatting
No big thing! I just wanted to state my opinion that retroactive changes in posts may be problematic, in specific if a later post expressly refers to its contents or to the contents of an original attachment. My answer did (point -2- mainly).
No ned of much ado in this simple case. The attachment was small enough. Anonymisation was simple and sufficient, I assume. (See new attachment. The downloaded original was still stored on my computer.) @RoryOF: Sorry that I probably caused unnecessary trouble. If you advise me with this respect I will try to change my behaviour. I will also delete the attachment again if you suggest to do so.Twiggy wrote:There were names in the attachment. But no indication of address or location. Perhaps in hindsight I should have submitted a small, general sample of what I was trying to resolve.
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
Re: [Solved] MATCH function in Conditional Formatting
No, Lupp: yours was a fair comment, as a missing attachment of a problem file renders much of the subsequent discussion (and hopefully, faultfinding) meaningless.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS