Sep 29 2021 09:37 AM
I am trying to add a fill color to cell 1 if cell 1 is included in the formula of a cell 2.
Also, it would need to remove fill color from cell 1 if it is removed from cell 2.
I have attached an example.
Is this doable?
Sep 29 2021 10:20 AM
I'm confused when you say "included in the formula ..." you mean the value as a result of the formula or the range (AC75) on the formula itself?
Sep 29 2021 10:29 AM
@Juliano-Petrukio The range of the formula.
Sep 29 2021 11:00 AM
SolutionI need to admit that it is the first time I see something like that.
Well, the conditional formatting formula would be something like that.
=FIND(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$",""),FORMULATEXT($B15))>0
Remember to change the reference where the "main" formula is. This case the formula is on B15
Find attachment.
Sep 29 2021 11:26 AM
This should work!
AND I admit I am not very fluent with excel.
I have attached the file in question.
Ideally, I would be able to assign a color to the total from the high, medium and low priority cells, and excel would magically fill in the background colors of all cells included in the range of the formula.
Sep 29 2021 12:16 PM
Sep 29 2021 12:46 PM
Sep 29 2021 12:50 PM
You just need to select the cells you want to apply the formatting
On Applies to you change the range you want to apply
Sep 29 2021 01:00 PM
Sep 29 2021 01:13 PM
Sep 29 2021 01:20 PM
I did virtually the same as you, with the exception that I used a column of defined names to identify the references rather than using ADDRESS and direct referencing. My feeling, though, was to write,
"yes, what the OP requires is technically possible using FORMULATEXT, but I wouldn't recommend it".
I would prefer to see a specific data field to hold the {"high","medium","low"} and drive the formula from the data rather than the reverse. What are your thoughts?
Sep 29 2021 01:28 PM
Sep 29 2021 02:47 PM
Just to 'come up with the goods' and demonstrate that I had come up with a similar solution.
Mine probably has higher set up costs because I used defined names, but, then, I use defined names for everything.
Sep 29 2021 11:00 AM
SolutionI need to admit that it is the first time I see something like that.
Well, the conditional formatting formula would be something like that.
=FIND(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$",""),FORMULATEXT($B15))>0
Remember to change the reference where the "main" formula is. This case the formula is on B15
Find attachment.