First, I have searched repeatedly for this, but I think the biggest problem is that I cannot figure out how to phrase the question properly. This is a huge post; trying to fit all of this into simple search is pretty hard. So here goes:
In google sheets, I have a list of things I'll call "options" on sheet2. I need the user to pick 4 of 8 of these options, and I think the best way is to use checkboxes in a second column. (It's possible this isn't the best solution. I'm open to suggestions.) It looks like this, but with fancy check boxes instead of the T/Fs:
Options Active? ------------------- Opt-A TRUE Opt-B FALSE Opt-C FALSE Opt-D TRUE Opt-E FALSE Opt-F TRUE Opt-G FALSE Opt-H TRUE
(I'll include Pic #1 at the end, but this should be fine.)
On sheet1, I have a long list of what I'll call "items" in the first column. Each item has exactly 2 different options (from the list on sheet2) associated with it, listed in the second and third columns. The fourth column displays how many options this item has that match with the options that the user selected on sheet 2 (as described in the previous paragraph). It looks like this.
Items ItemOpt 1 ItemOpt 2 Option Matches ---------------------------------------------- Item-1 Opt-A Opt-E 1 Item-2 Opt-B Opt-G 0 Item-3 Opt-A Opt-D 2 Item-4 Opt-C Opt-E 0 Item-5 Opt-B Opt-D 1 Item-6 Opt-E Opt-F 1 Item-7 Opt-F Opt-H 2 Item-8 Opt-D Opt-E 1
(Again, I'll include Pic #2 at the end.)
So far, I've actually managed to get all of that working! Mirabile dictu! But what I'd like to do is, on Sheet1, in those two "ItemOpt" columns, I'd like to conditionally format cells that match the options selected on sheet2. So, for instance, for Item-1 it would make the background green for the cell after Item-1 containing "Opt-A" but leave the cell with "Opt-E" alone. Item-4 would have neither of the two following cells highlighted, and Item-7 would both the following cells (with "Opt-F" and "Opt-H" in them) have a green background. I'll post a pic of this at the bottom, but I don't know how to colour text here, so I can't do a plaintext version.
Anyway, this is the part where I'm stumped. How do I say in a given cell "check column A of sheet 2 for the contents of this cell. When you find it, see if the cell in column B of that same row has a check (is TRUE). If so, make this cell green."?
Is this even possible? Again, huge part of the problem is that I don't know spreadsheet terminology, so I can't formulate a good search.
I'd really appreciate any help folks could give. Even pointing me to a command or feature I don't know about, or something like "you can't use COUNTIFS on the same range twice without using ArrayFormula" (I learned that today) could be really helpful.
Here are the pics, as promised: