Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 12 Jul 2020 14:42:13 +0200IF + Conditional formattinghttps://ask.libreoffice.org/en/question/254613/if-conditional-formatting/How do I accomplish this task?
Suppose I have 1 reference cell R1, and I have 2 columns, C and D, just to make it simple, let's assume it has only 3 rows,
`C1=<5`, `C2=5-10`, `C3>10`, if my R1 is `6`, then I'd like to put a mark (O for instance) in D2, with background yellow, but if my R1 is `3`, then D1 should have O with yellow color. My initial thought is having an IF on D column to test the values and put an O, and then conditional formatting to change the yellow background, I'm still learning CF (Conditional Formatting), so not sure how to accomplish this. can someone please point me to the right direction? Or is there any tutorial explaining details of CF?
ps: I've attached an example with 5 rows to show what I'd like to accomplish.[C:\fakepath\15943082546679422.ods](/upfiles/15943861729321792.ods)Fri, 10 Jul 2020 15:07:56 +0200https://ask.libreoffice.org/en/question/254613/if-conditional-formatting/Comment by michaelbr for <p>How do I accomplish this task?</p>
<p>Suppose I have 1 reference cell R1, and I have 2 columns, C and D, just to make it simple, let's assume it has only 3 rows,
<code>C1=<5</code>, <code>C2=5-10</code>, <code>C3>10</code>, if my R1 is <code>6</code>, then I'd like to put a mark (O for instance) in D2, with background yellow, but if my R1 is <code>3</code>, then D1 should have O with yellow color. My initial thought is having an IF on D column to test the values and put an O, and then conditional formatting to change the yellow background, I'm still learning CF (Conditional Formatting), so not sure how to accomplish this. can someone please point me to the right direction? Or is there any tutorial explaining details of CF?
ps: I've attached an example with 5 rows to show what I'd like to accomplish.<a href="/upfiles/15943861729321792.ods">C:\fakepath\15943082546679422.ods</a></p>
https://ask.libreoffice.org/en/question/254613/if-conditional-formatting/?comment=254714#post-id-254714Sorry Opaque, the description above is different from the spreadsheet, I used a short description to simplify my explanation, and I'm afraid that I made it worse.Sat, 11 Jul 2020 14:00:59 +0200https://ask.libreoffice.org/en/question/254613/if-conditional-formatting/?comment=254714#post-id-254714Comment by Opaque for <p>How do I accomplish this task?</p>
<p>Suppose I have 1 reference cell R1, and I have 2 columns, C and D, just to make it simple, let's assume it has only 3 rows,
<code>C1=<5</code>, <code>C2=5-10</code>, <code>C3>10</code>, if my R1 is <code>6</code>, then I'd like to put a mark (O for instance) in D2, with background yellow, but if my R1 is <code>3</code>, then D1 should have O with yellow color. My initial thought is having an IF on D column to test the values and put an O, and then conditional formatting to change the yellow background, I'm still learning CF (Conditional Formatting), so not sure how to accomplish this. can someone please point me to the right direction? Or is there any tutorial explaining details of CF?
ps: I've attached an example with 5 rows to show what I'd like to accomplish.<a href="/upfiles/15943861729321792.ods">C:\fakepath\15943082546679422.ods</a></p>
https://ask.libreoffice.org/en/question/254613/if-conditional-formatting/?comment=254623#post-id-254623Why did you put your `O` into `C7`, while your description doesn't tell about what to happen if the reference is 16. From my perspective it looks a bit you want the row describing the range into which the value of your reference cell falls into having an `O` (in colum `C` if Male / in column `D` if Female).Fri, 10 Jul 2020 15:59:51 +0200https://ask.libreoffice.org/en/question/254613/if-conditional-formatting/?comment=254623#post-id-254623Answer by Opaque for <p>How do I accomplish this task?</p>
<p>Suppose I have 1 reference cell R1, and I have 2 columns, C and D, just to make it simple, let's assume it has only 3 rows,
<code>C1=<5</code>, <code>C2=5-10</code>, <code>C3>10</code>, if my R1 is <code>6</code>, then I'd like to put a mark (O for instance) in D2, with background yellow, but if my R1 is <code>3</code>, then D1 should have O with yellow color. My initial thought is having an IF on D column to test the values and put an O, and then conditional formatting to change the yellow background, I'm still learning CF (Conditional Formatting), so not sure how to accomplish this. can someone please point me to the right direction? Or is there any tutorial explaining details of CF?
ps: I've attached an example with 5 rows to show what I'd like to accomplish.<a href="/upfiles/15943861729321792.ods">C:\fakepath\15943082546679422.ods</a></p>
https://ask.libreoffice.org/en/question/254613/if-conditional-formatting/?answer=254624#post-id-254624Hello,
See the following extension of the file I've provided with the answer to your last question: [C:\fakepath\ConditionalMaleFemale+IF.ods](/upfiles/15943900119350577.ods)
Now test using drop down in `A2`, which only handles the two cases (`3` and `6`) of your description. Formlual in
C2: `=IF(A2=3;"O";"")`
D2: `=IF(A2=6;"O";"")`
And there is an additional Conditional Formatting for Range `C2:D2`.
Hope that helps.
Fri, 10 Jul 2020 16:07:02 +0200https://ask.libreoffice.org/en/question/254613/if-conditional-formatting/?answer=254624#post-id-254624Comment by michaelbr for <p>Hello,</p>
<p>See the following extension of the file I've provided with the answer to your last question: <a href="/upfiles/15943900119350577.ods">C:\fakepath\ConditionalMaleFemale+IF.ods</a> </p>
<p>Now test using drop down in <code>A2</code>, which only handles the two cases (<code>3</code> and <code>6</code>) of your description. Formlual in
C2: <code>=IF(A2=3;"O";"")</code>
D2: <code>=IF(A2=6;"O";"")</code></p>
<p>And there is an additional Conditional Formatting for Range <code>C2:D2</code>.</p>
<p>Hope that helps.</p>
https://ask.libreoffice.org/en/question/254613/if-conditional-formatting/?comment=254823#post-id-254823Thanks so much for this detailed explanation, now it's clear.Sun, 12 Jul 2020 14:42:13 +0200https://ask.libreoffice.org/en/question/254613/if-conditional-formatting/?comment=254823#post-id-254823Comment by Opaque for <p>Hello,</p>
<p>See the following extension of the file I've provided with the answer to your last question: <a href="/upfiles/15943900119350577.ods">C:\fakepath\ConditionalMaleFemale+IF.ods</a> </p>
<p>Now test using drop down in <code>A2</code>, which only handles the two cases (<code>3</code> and <code>6</code>) of your description. Formlual in
C2: <code>=IF(A2=3;"O";"")</code>
D2: <code>=IF(A2=6;"O";"")</code></p>
<p>And there is an additional Conditional Formatting for Range <code>C2:D2</code>.</p>
<p>Hope that helps.</p>
https://ask.libreoffice.org/en/question/254613/if-conditional-formatting/?comment=254719#post-id-254719Common problem to understand how Conditional Fomatting internally works for ranges: You never need to specify the range in a formula but the formula for the **upper left corner** cell of a range.
Condition 1: `Formula is` `C2="O"` **Range** `C2:D2`
The Conditional Formatting algorithm assures, that the formula is adapted for each cell of the defined range (here: `C2:D2`). Though you defined the rule only once (for the upper left corner cell of the range, internally the following happens:
- Ah - have to condiitional format `C2:D2`, lets start with `C2` (upper left corner)
- Check the rule `C2="O"` and do the required formatting, if rule yields true
- Move to `D2` (next cell of the range)
- **adapt the rule** to `D2="O"`
- Check the adapted rule `D2="O"` and do the required formatting, if rule yields trueSat, 11 Jul 2020 14:25:54 +0200https://ask.libreoffice.org/en/question/254613/if-conditional-formatting/?comment=254719#post-id-254719Comment by michaelbr for <p>Hello,</p>
<p>See the following extension of the file I've provided with the answer to your last question: <a href="/upfiles/15943900119350577.ods">C:\fakepath\ConditionalMaleFemale+IF.ods</a> </p>
<p>Now test using drop down in <code>A2</code>, which only handles the two cases (<code>3</code> and <code>6</code>) of your description. Formlual in
C2: <code>=IF(A2=3;"O";"")</code>
D2: <code>=IF(A2=6;"O";"")</code></p>
<p>And there is an additional Conditional Formatting for Range <code>C2:D2</code>.</p>
<p>Hope that helps.</p>
https://ask.libreoffice.org/en/question/254613/if-conditional-formatting/?comment=254641#post-id-254641Thanks Opaque again for your help, I tried to use `Formula is =` `C$2:D$2="O"`, doesn't work, why? What's the difference?
Maybe `Formula is` only can test single cell?Fri, 10 Jul 2020 19:23:11 +0200https://ask.libreoffice.org/en/question/254613/if-conditional-formatting/?comment=254641#post-id-254641