Format if Cell Contains Specific Set of numbers (not a range)

:snoo: Solved

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

54
Hxcwinner•

This should work as a custom formula for conditional formatting,

=COUNTIF($A$3:$A,B3)>0

4
zero_sheets_given149•

Perhaps >0 just in case they put a duplicate line

2
Hxcwinner•

Good call! Didn’t consider duplicates, edited original post

3
CyberphilOP•

Ahh, brilliantly simple! Thank you so much!

This will save a lot of time.

1