0

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:

Pic #1: User selects 4 of the 8 options

Pic #2: The list of items and each item's two options

Pic #3: Once the user has selected their options on sheet2, those options are highlighted for items on sheet1

1
0

As Ruben mentioned Indirect() is the secret to making the conditional formatting UI look at a different sheet (since it can't do that normally).

But to more directly answer your specific question this is what I would do.

  • On your Sheet2 Checkboxes.. I would add a column next to that (or place of your choosing) to create a list that filters out the boxes that are checked (true). =Filter(A2:A,B2:B=True). This will give you a dynamic list of what is actively checked to more easily do your formatting.

  • In the range you wish to format, I would use the custom formula for conditional formatting as follows =COUNTIF(indirect("Sheet2!C2:C"), $B2)>0 . This should highlight whatever you have checked off on sheet 2 in Sheet 1 column B

  • Repeat for column C changing the conditional formatting formula to =COUNTIF(indirect("Sheet2!C2:C"), $C2)>0

Here is a sheet for you to make a copy of to explore this in more detail. https://docs.google.com/spreadsheets/d/1vbiIATW-b37ICt2XFlXkC5hQ8MBTlLxrgrOoBefJ3zw/edit?usp=sharing

Hope this helps.

1
  • Yes, the "=Filter(A2:A,B2:B=True)" was the part I kept trying to find to get a work-around. I also needed the COUNTIF and figured it out while you were posting that answer! Thank you! I've spent too many hours on this, and you just saved me another [cough cough, don't make me admit how many] more hours!! I appreciate it!
    – Pirate Dan
    Dec 9 '20 at 0:47
0

So, here's how I ended up handling all the issues.

I didn't use "INDIRECT" because I didn't see Rubén's post until after I'd found a work-around (thank you, Rubén!). What I did (which is basically the same as the first answer reply, but putting the dynamic list on sheet1) was to copy the selected options from sheet2 to sheet1 using a previously unused column (K) on sheet1, and this:

=Filter(Sheet2!B2:B9,Sheet2!A2:A9=True)

(Thanks to enter link description here for that!)Then I conditionally formatted the two option columns (B and C) based off of column K. In conditional formatting, I used separate custom formulae for columns B and C. (Not sure why this was necessary, but it apparently was) to then highlight the matches:

=COUNTIF(K$3:K$10, B2)
=COUNTIF(K$3:K$10, C2)

And then just used over the entire vertical range of the sheet.

It wasn't part of my question, but in case it's useful to anybody, the formula for the "option matches" column looks like this:

=Arrayformula(
  Sum(
   COUNTIFS(K$3:$10,{C2,D2})
  )
)

And just copied it down the column.

Thanks again to the folks who helped. I really hope that this helps somebody else down the line!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.