Use case: Diet tracker on a spreadsheet. I have two tabs: one that is what I eat everyday, another which lists foods that I should avoid. What I want to do is highlight foods that I ate which I should’ve avoided.
Sample file attached Sample.ods
Edit: The indicator of a solved question is the green check mark on an accepted / preferred answer. There is no need to modify the title.
Hello,
from my perspective: You can’t achieve your goal using Conditional Formatting, if you don’t split each food you ate into a separate column / cell. In other words: don’t use a single cell containing multiple foods separated by , [comma].
See the following sample file, which shows how it works based on the above:
SampleModified.ods
Condition
-
Formula is:
MATCH(C2;$FoodsToAvoid.$A$1:$A$11;0)($A$1:$A$11requires adaption as list of food grows) -
Range:
C2:G11(of course requires adaption to real size of this FoodLog)
Note
If you want to use your original design, you probably need a macro.
Hope that helps.
Thank you so much for coming back to me so quickly Opaque. It helps me to learn but I have to choose the other answer based on my needs.
I think it’s possible if one of the list has one item by line.
A formula like: SUMPRODUCT(COUNTIFS(C2;"*"&FoodsToAvoid.$A$2:$A$4&"*"))
on the condition works fine, SUMPRODUCT gets the sum of matches number, forcing an array formula for COUNTIFS.
Attached the sample with it.
Thank you so much for making it work based on my needs!