Format if Cell Contains Specific Set of numbers (not a range)
Hey Reddit,
I'm trying to create a spreadsheet that figures out if we have the right parts in stock (gears specifically). I have a list of gears (tooth counts), and a calculation that determines the gears we need based on center distances. How can I conditionally format the range of gears we need to show if we have that gear in stock or not? This seems so simple, but I can't figure out how to do this.
An example spreadsheet to show you what I'm working with:
https://docs.google.com/spreadsheets/d/1wRD-1o6eLphZ3jnNpCLLQmQYQvIywp0gJ-Dd0CCFB-c/edit?usp=sharing
Ideally the array will show which gears in the needed array already exist in the left most column by changing colors or something.
Thank you!
Edit: I found something that kind of works, but would be extremely tedious for a large dataset with similar options.
I used this this logical operator:
=or(B3=$A$3,B3=$A$4,B3=$A$5,B3=$A$6,B3=$A$7,B3=$A$8)
The obvious problem is I would need to add a checking condition for every single gear we have in stock. This is not a reasonable option.
Posted Feb 23, 2020, 1:31 AM UTC · edited
This should work as a custom formula for conditional formatting,
=COUNTIF($A$3:$A,B3)>0
Perhaps >0 just in case they put a duplicate line
Good call! Didn’t consider duplicates, edited original post
Ahh, brilliantly simple! Thank you so much!
This will save a lot of time.