Ask LibreOffice  RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 08 Mar 2020 00:48:51 +0100Conditional formatting for cells used in functionhttps://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/Dear all, i would like toask for help with following:
I have calculation for costs of the building construction, where in rows are parts of construction in one cell and prices in next cell. F.e. walls: 100 EUR, roof: 200 EUR and door type 1: 300 EUR and door type 2: 400 EUR. At the end of the list I count price of items used in particular construction  f.e. walls, roof and door type 1 (i do not calculate "door type 2").
Is there any way, how to higlight cells, which where used in my formula  in this case 100 EUR, 200 EUR and 300 EUR value? List is long and I would like to see which items are calculated and which not (without clicking on cell with formula)
I watched several tutorials, but did not find answer.
Thanks!
Stan
UPDATE: Thanks to one post I realise I could make it much easier and do it othe way around  first highlight items and then count them. I add file with first idea, which is probably too complicated, so i would do it second way. If some of you would find how to find fuction to count price based on selected items without "side calculation" column would be big help, thanks! [C:\fakepath\Sample.xls](/upfiles/15834049618958708.xls)Wed, 04 Mar 2020 15:40:19 +0100https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/Comment by Stani for <p>Dear all, i would like toask for help with following:</p>
<p>I have calculation for costs of the building construction, where in rows are parts of construction in one cell and prices in next cell. F.e. walls: 100 EUR, roof: 200 EUR and door type 1: 300 EUR and door type 2: 400 EUR. At the end of the list I count price of items used in particular construction  f.e. walls, roof and door type 1 (i do not calculate "door type 2").
Is there any way, how to higlight cells, which where used in my formula  in this case 100 EUR, 200 EUR and 300 EUR value? List is long and I would like to see which items are calculated and which not (without clicking on cell with formula)</p>
<p>I watched several tutorials, but did not find answer.
Thanks!</p>
<p>Stan</p>
<p>UPDATE: Thanks to one post I realise I could make it much easier and do it othe way around  first highlight items and then count them. I add file with first idea, which is probably too complicated, so i would do it second way. If some of you would find how to find fuction to count price based on selected items without "side calculation" column would be big help, thanks! <a href="/upfiles/15834049618958708.xls">C:\fakepath\Sample.xls</a></p>
https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232608#postid232608You was right  there were 2 issues and you was right, Lupp got it right.
Thanks!Fri, 06 Mar 2020 16:21:38 +0100https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232608#postid232608Comment by Inclement for <p>Dear all, i would like toask for help with following:</p>
<p>I have calculation for costs of the building construction, where in rows are parts of construction in one cell and prices in next cell. F.e. walls: 100 EUR, roof: 200 EUR and door type 1: 300 EUR and door type 2: 400 EUR. At the end of the list I count price of items used in particular construction  f.e. walls, roof and door type 1 (i do not calculate "door type 2").
Is there any way, how to higlight cells, which where used in my formula  in this case 100 EUR, 200 EUR and 300 EUR value? List is long and I would like to see which items are calculated and which not (without clicking on cell with formula)</p>
<p>I watched several tutorials, but did not find answer.
Thanks!</p>
<p>Stan</p>
<p>UPDATE: Thanks to one post I realise I could make it much easier and do it othe way around  first highlight items and then count them. I add file with first idea, which is probably too complicated, so i would do it second way. If some of you would find how to find fuction to count price based on selected items without "side calculation" column would be big help, thanks! <a href="/upfiles/15834049618958708.xls">C:\fakepath\Sample.xls</a></p>
https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232419#postid232419Stan, are there two separate problems?
1. Marking the items
2. Calculating the total price of the marked items
For (1), if there's a fixed rule for which items you mark  for instance, if you want to select *all* of the "wall type 1" items from your list, and *never* "wall type 2," you can make formulas to do that:
`if item=wall type 1, then ___`.
But if there's no fixed rule, you can't make the spreadsheet do it automatically.
*[Edit: see Lupp's solution below, using SUMIF()]* For (2), it seems you don't want the extra column, the side calculation. But that might be the easiest way. If you try to put calculations for many rows into one formula in one cell (your "total" cell), you'll have a very long formula. That will be hard to write, maintain, and debug.
An option might be to move the side calculation to a separate sheet, so it's out of the way.
(Could arrays help? That's a question for anyone who knows. I don't.)Thu, 05 Mar 2020 15:20:51 +0100https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232419#postid232419Comment by Stani for <p>Dear all, i would like toask for help with following:</p>
<p>I have calculation for costs of the building construction, where in rows are parts of construction in one cell and prices in next cell. F.e. walls: 100 EUR, roof: 200 EUR and door type 1: 300 EUR and door type 2: 400 EUR. At the end of the list I count price of items used in particular construction  f.e. walls, roof and door type 1 (i do not calculate "door type 2").
Is there any way, how to higlight cells, which where used in my formula  in this case 100 EUR, 200 EUR and 300 EUR value? List is long and I would like to see which items are calculated and which not (without clicking on cell with formula)</p>
<p>I watched several tutorials, but did not find answer.
Thanks!</p>
<p>Stan</p>
<p>UPDATE: Thanks to one post I realise I could make it much easier and do it othe way around  first highlight items and then count them. I add file with first idea, which is probably too complicated, so i would do it second way. If some of you would find how to find fuction to count price based on selected items without "side calculation" column would be big help, thanks! <a href="/upfiles/15834049618958708.xls">C:\fakepath\Sample.xls</a></p>
https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232372#postid232372Thanks, i added file.Thu, 05 Mar 2020 11:40:32 +0100https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232372#postid232372Comment by Opaque for <p>Dear all, i would like toask for help with following:</p>
<p>I have calculation for costs of the building construction, where in rows are parts of construction in one cell and prices in next cell. F.e. walls: 100 EUR, roof: 200 EUR and door type 1: 300 EUR and door type 2: 400 EUR. At the end of the list I count price of items used in particular construction  f.e. walls, roof and door type 1 (i do not calculate "door type 2").
Is there any way, how to higlight cells, which where used in my formula  in this case 100 EUR, 200 EUR and 300 EUR value? List is long and I would like to see which items are calculated and which not (without clicking on cell with formula)</p>
<p>I watched several tutorials, but did not find answer.
Thanks!</p>
<p>Stan</p>
<p>UPDATE: Thanks to one post I realise I could make it much easier and do it othe way around  first highlight items and then count them. I add file with first idea, which is probably too complicated, so i would do it second way. If some of you would find how to find fuction to count price based on selected items without "side calculation" column would be big help, thanks! <a href="/upfiles/15834049618958708.xls">C:\fakepath\Sample.xls</a></p>
https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232267#postid232267How does you formula look like(or how do you select, which items to be included in total costs and which should not )? Could you upload a short sample file showing your structure and method of calculation?Wed, 04 Mar 2020 17:40:06 +0100https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232267#postid232267Answer by Lupp for <p>Dear all, i would like toask for help with following:</p>
<p>I have calculation for costs of the building construction, where in rows are parts of construction in one cell and prices in next cell. F.e. walls: 100 EUR, roof: 200 EUR and door type 1: 300 EUR and door type 2: 400 EUR. At the end of the list I count price of items used in particular construction  f.e. walls, roof and door type 1 (i do not calculate "door type 2").
Is there any way, how to higlight cells, which where used in my formula  in this case 100 EUR, 200 EUR and 300 EUR value? List is long and I would like to see which items are calculated and which not (without clicking on cell with formula)</p>
<p>I watched several tutorials, but did not find answer.
Thanks!</p>
<p>Stan</p>
<p>UPDATE: Thanks to one post I realise I could make it much easier and do it othe way around  first highlight items and then count them. I add file with first idea, which is probably too complicated, so i would do it second way. If some of you would find how to find fuction to count price based on selected items without "side calculation" column would be big help, thanks! <a href="/upfiles/15834049618958708.xls">C:\fakepath\Sample.xls</a></p>
https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?answer=232306#postid232306There isn't a "trace mode" among the mods of Conditional Formatting and also no standard function helping to get a value of a meaning like "isCell1referencedInCell2()".
Thus you would need to write such a function for your purposes, and this would not be very well supported by the API, I'm afraid.
A spreadsheet will know precedents and dependents of any cell to be able to maintain recalculation trees. The only tool I know which gives the user access to this knowledge is the `>Tools>Detective` group of menu items.
You can probably use it to get what you want.
But: The selection of items to be included with a SUM or in another way in a formula should anyway be made by cell values referenced by conditions (comparisons e.g.). As soon as your sheet does it this way, you can use the same conditions with CF.
===Editing 20100305 about 14:25 UTC===
I attach [this "ask232256selectForSumAndCF.ods" file,](/upfiles/15834182541103795.ods) a reworked version of the document the OQer supplied by editing his question.Wed, 04 Mar 2020 21:58:23 +0100https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?answer=232306#postid232306Comment by Lupp for <p>There isn't a "trace mode" among the mods of Conditional Formatting and also no standard function helping to get a value of a meaning like "isCell1referencedInCell2()". </p>
<p>Thus you would need to write such a function for your purposes, and this would not be very well supported by the API, I'm afraid. </p>
<p>A spreadsheet will know precedents and dependents of any cell to be able to maintain recalculation trees. The only tool I know which gives the user access to this knowledge is the <code>>Tools>Detective</code> group of menu items. <br>
You can probably use it to get what you want. </p>
<p>But: The selection of items to be included with a SUM or in another way in a formula should anyway be made by cell values referenced by conditions (comparisons e.g.). As soon as your sheet does it this way, you can use the same conditions with CF. </p>
<p>===Editing 20100305 about 14:25 UTC=== <br>
I attach <a href="/upfiles/15834182541103795.ods">this "ask232256selectForSumAndCF.ods" file,</a> a reworked version of the document the OQer supplied by editing his question.</p>
https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232792#postid232792`>Format>Conditional>Manage>> Edit >> Cell Range`
You can directly edit the cell range(s) there to which the CF will be applied. Relative addressing in `Formula is` mode is always relative to the topmost leftmost cell of all the ranges.Sun, 08 Mar 2020 00:48:51 +0100https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232792#postid232792Comment by Stani for <p>There isn't a "trace mode" among the mods of Conditional Formatting and also no standard function helping to get a value of a meaning like "isCell1referencedInCell2()". </p>
<p>Thus you would need to write such a function for your purposes, and this would not be very well supported by the API, I'm afraid. </p>
<p>A spreadsheet will know precedents and dependents of any cell to be able to maintain recalculation trees. The only tool I know which gives the user access to this knowledge is the <code>>Tools>Detective</code> group of menu items. <br>
You can probably use it to get what you want. </p>
<p>But: The selection of items to be included with a SUM or in another way in a formula should anyway be made by cell values referenced by conditions (comparisons e.g.). As soon as your sheet does it this way, you can use the same conditions with CF. </p>
<p>===Editing 20100305 about 14:25 UTC=== <br>
I attach <a href="/upfiles/15834182541103795.ods">this "ask232256selectForSumAndCF.ods" file,</a> a reworked version of the document the OQer supplied by editing his question.</p>
https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232623#postid232623Lupp, big gudos to you. It was exactly what I needed.
Function works very good. I was also able to use CF, but just on one row. I did not find a way how to apply it on whole table  I think because of lack of my knowledge, I will check some tutorials. If you could recomend sth I appreciate.
Also thanks for extra tips, i will keep them on mind. Would you also keep open office format, when sharing table with excel users?
Thanks again. Work you /all/ do is great and I am amazed and thankful I could just come here and get such a peace of advice.
StaniFri, 06 Mar 2020 17:23:06 +0100https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232623#postid232623Comment by Inclement for <p>There isn't a "trace mode" among the mods of Conditional Formatting and also no standard function helping to get a value of a meaning like "isCell1referencedInCell2()". </p>
<p>Thus you would need to write such a function for your purposes, and this would not be very well supported by the API, I'm afraid. </p>
<p>A spreadsheet will know precedents and dependents of any cell to be able to maintain recalculation trees. The only tool I know which gives the user access to this knowledge is the <code>>Tools>Detective</code> group of menu items. <br>
You can probably use it to get what you want. </p>
<p>But: The selection of items to be included with a SUM or in another way in a formula should anyway be made by cell values referenced by conditions (comparisons e.g.). As soon as your sheet does it this way, you can use the same conditions with CF. </p>
<p>===Editing 20100305 about 14:25 UTC=== <br>
I attach <a href="/upfiles/15834182541103795.ods">this "ask232256selectForSumAndCF.ods" file,</a> a reworked version of the document the OQer supplied by editing his question.</p>
https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232424#postid232424Oh hooray. I think Lupp's got it now, with the SUMIF() function. :)Thu, 05 Mar 2020 15:33:24 +0100https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232424#postid232424Comment by Stani for <p>There isn't a "trace mode" among the mods of Conditional Formatting and also no standard function helping to get a value of a meaning like "isCell1referencedInCell2()". </p>
<p>Thus you would need to write such a function for your purposes, and this would not be very well supported by the API, I'm afraid. </p>
<p>A spreadsheet will know precedents and dependents of any cell to be able to maintain recalculation trees. The only tool I know which gives the user access to this knowledge is the <code>>Tools>Detective</code> group of menu items. <br>
You can probably use it to get what you want. </p>
<p>But: The selection of items to be included with a SUM or in another way in a formula should anyway be made by cell values referenced by conditions (comparisons e.g.). As soon as your sheet does it this way, you can use the same conditions with CF. </p>
<p>===Editing 20100305 about 14:25 UTC=== <br>
I attach <a href="/upfiles/15834182541103795.ods">this "ask232256selectForSumAndCF.ods" file,</a> a reworked version of the document the OQer supplied by editing his question.</p>
https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232375#postid232375Thank you Lupp. Unfortunatelly, my skills doent allow me to fully understand what I should do, but it definitelly helped me to realise that it was maybe too complicated. I added sample table in original post, if you would like to see my thinking about other way how to get what I want.
Cheers
StanThu, 05 Mar 2020 11:51:49 +0100https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232375#postid232375Answer by Schiavinatto for <p>Dear all, i would like toask for help with following:</p>
<p>I have calculation for costs of the building construction, where in rows are parts of construction in one cell and prices in next cell. F.e. walls: 100 EUR, roof: 200 EUR and door type 1: 300 EUR and door type 2: 400 EUR. At the end of the list I count price of items used in particular construction  f.e. walls, roof and door type 1 (i do not calculate "door type 2").
Is there any way, how to higlight cells, which where used in my formula  in this case 100 EUR, 200 EUR and 300 EUR value? List is long and I would like to see which items are calculated and which not (without clicking on cell with formula)</p>
<p>I watched several tutorials, but did not find answer.
Thanks!</p>
<p>Stan</p>
<p>UPDATE: Thanks to one post I realise I could make it much easier and do it othe way around  first highlight items and then count them. I add file with first idea, which is probably too complicated, so i would do it second way. If some of you would find how to find fuction to count price based on selected items without "side calculation" column would be big help, thanks! <a href="/upfiles/15834049618958708.xls">C:\fakepath\Sample.xls</a></p>
https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?answer=232523#postid232523Hello @Stani, follow a suggestion, sum is with reference to cell C31.
Formula is matrix, after typing Ctrl + Shift + Enter
![image description](/upfiles/1583457722580953.png)


**ATTENTION:** If you would like to give more details to your question, use **edit** in question or **add a comment** below. Thank you.
If the answer met your need, please click on the ball <img alt="Descrição da imagem" src="/upfiles/14057372472796181.jpg" style="maxwidth: 65px; height: auto;"> to the left of the answer, to finish the question.
Fri, 06 Mar 2020 02:23:14 +0100https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?answer=232523#postid232523Comment by Stani for <p>Hello <a href="/en/users/77705/stani/">@Stani</a>, follow a suggestion, sum is with reference to cell C31.</p>
<p>Formula is matrix, after typing Ctrl + Shift + Enter</p>
<p><img src="/upfiles/1583457722580953.png" alt="image description"></p>
<h2></h2>
<p><strong>ATTENTION:</strong> If you would like to give more details to your question, use <strong>edit</strong> in question or <strong>add a comment</strong> below. Thank you. </p>
<p>If the answer met your need, please click on the ball <img src="/upfiles/14057372472796181.jpg" alt="Descrição da imagem"> to the left of the answer, to finish the question. </p>
https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232749#postid232749Thank you Schiavinatto,
could you also upload file with your suggestion?Sat, 07 Mar 2020 16:30:52 +0100https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232749#postid232749Answer by Inclement for <p>Dear all, i would like toask for help with following:</p>
<p>I have calculation for costs of the building construction, where in rows are parts of construction in one cell and prices in next cell. F.e. walls: 100 EUR, roof: 200 EUR and door type 1: 300 EUR and door type 2: 400 EUR. At the end of the list I count price of items used in particular construction  f.e. walls, roof and door type 1 (i do not calculate "door type 2").
Is there any way, how to higlight cells, which where used in my formula  in this case 100 EUR, 200 EUR and 300 EUR value? List is long and I would like to see which items are calculated and which not (without clicking on cell with formula)</p>
<p>I watched several tutorials, but did not find answer.
Thanks!</p>
<p>Stan</p>
<p>UPDATE: Thanks to one post I realise I could make it much easier and do it othe way around  first highlight items and then count them. I add file with first idea, which is probably too complicated, so i would do it second way. If some of you would find how to find fuction to count price based on selected items without "side calculation" column would be big help, thanks! <a href="/upfiles/15834049618958708.xls">C:\fakepath\Sample.xls</a></p>
https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?answer=232330#postid232330Would something like this work?
![image description](/upfiles/15833789535327701.png)Thu, 05 Mar 2020 04:31:05 +0100https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?answer=232330#postid232330Comment by Stani for <p>Would something like this work? </p>
<p><img src="/upfiles/15833789535327701.png" alt="image description"></p>
https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232377#postid232377Inclement,
however this is not what I was asking for, it brought me to idea do it other way and make working with table more user friendly.
Thanks
StanThu, 05 Mar 2020 11:54:33 +0100https://ask.libreoffice.org/en/question/232256/conditionalformattingforcellsusedinfunction/?comment=232377#postid232377