Ask Your Question

Conditional formatting for cells used in function

asked 2020-03-04 15:40:19 +0100

Stani gravatar image

updated 2020-03-05 11:44:47 +0100

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!


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

edit retag flag offensive close merge delete



How 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?

Opaque gravatar imageOpaque ( 2020-03-04 17:40:06 +0100 )edit

Thanks, i added file.

Stani gravatar imageStani ( 2020-03-05 11:40:32 +0100 )edit

Stan, 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 ...(more)

Inclement gravatar imageInclement ( 2020-03-05 15:20:51 +0100 )edit

You was right - there were 2 issues and you was right, Lupp got it right. Thanks!

Stani gravatar imageStani ( 2020-03-06 16:21:38 +0100 )edit

3 Answers

Sort by » oldest newest most voted

answered 2020-03-04 21:58:23 +0100

Lupp gravatar image

updated 2020-03-05 15:26:31 +0100

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()".

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 2010-03-05 about 14:25 UTC===
I attach this "ask232256selectForSumAndCF.ods" file, a reworked version of the document the OQer supplied by editing his question.

edit flag offensive delete link more


Thank 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 Stan

Stani gravatar imageStani ( 2020-03-05 11:51:49 +0100 )edit

Oh hooray. I think Lupp's got it now, with the SUMIF() function. :)

Inclement gravatar imageInclement ( 2020-03-05 15:33:24 +0100 )edit

Lupp, 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. Stani

Stani gravatar imageStani ( 2020-03-06 17:23:06 +0100 )edit

>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.

Lupp gravatar imageLupp ( 2020-03-08 00:48:51 +0100 )edit

answered 2020-03-05 04:31:05 +0100

Inclement gravatar image

Would something like this work?

image description

edit flag offensive delete link more


Inclement, 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 Stan

Stani gravatar imageStani ( 2020-03-05 11:54:33 +0100 )edit

answered 2020-03-06 02:23:14 +0100

Hello @Stani, follow a suggestion, sum is with reference to cell C31.

Formula is matrix, after typing Ctrl + Shift + Enter

image description


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 Descrição da imagem to the left of the answer, to finish the question.

edit flag offensive delete link more


Thank you Schiavinatto, could you also upload file with your suggestion?

Stani gravatar imageStani ( 2020-03-07 16:30:52 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-03-04 15:40:19 +0100

Seen: 156 times

Last updated: Mar 06 '20