Conditional formatting for cells used in function

[Stani1]

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! Sample.xls

[anon73440385]

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?

[Stani1]

Thanks, i added file.

[Inclement]

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

[Stani1]

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

[Lupp]

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.

[Stani1]

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

[Inclement]

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

[Stani1]

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

[Lupp]

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

[Inclement]

Would something like this work?

[Stani1]

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

[schiavinatto]

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

Formula is matrix, after typing Ctrl + Shift + Enter

---

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 to the left of the answer, to finish the question.

[Stani1]

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