Sheets lookup on conditional highlight colour

0
votes

Is it possible to check a cell's conditional highlight colour and act accordingly?

I have a list of Names where some people belong to the "Yellow" team and some the "Pink" team. The Yellow team get more points allocated depending on their level (Cell B3).

Referring to the screenshots attached, I can't work out how to direct the lookup to the appropriate column of points per level in 'Sheet2' relative to the chosen Name. Conditional highlighting (see screenshot) sets the cell colour according to the Name input into cell B2.

Cell B6 contains the following formula: =vlookup(B3,Sheet2!A1:E11,match(B2,Sheet2!A1:E1,0),)

The formula worked when the reference table had Column B named as "Bob" but that is not what I want. I want the B6 formula to realize which colour team the B2 Name is and pick from the appropriate data set in 'Sheet2'.

Sheet1

Sheet2 reference data

Conditional Highlight parameters

Any help appreciated if it is at all possible to lookup against cell highlight conditions. Thanks!

2
google-apps-scriptgoogle-sheetslookup
Do you specifically need to match color, or a set of names you could put in a range? – man-teiv
I need to lookup depending on yellow or pink. Ultimately, there could be 30 names in the list but only ever 2 teams (yellow and pink) with the 2 data sets of points. The names will always be static to a team colour, so if that is a direction to go... – chop
You would need a script for that, using the getBackground(). So I would suggest you also add the tag google-apps-script and also delete the excel tag – marikamitsos

2 Answers

2
votes

There's no direct way of doing it. You might want to use the getBackground() function in Google apps script, but I don't think it's worth using scripts for this case. That would mean making the thing more complicated and less maintainable.

The easiest way you can achieve it is by using a helper cell.

enter image description here

You'll need the two teams listed in two columns. I'll assume to make things simple to have the two ranges E2:E30 for yellow names and F2:F30 for pink names

Add Color in cell A4 and the following formula in B4:

=IF(ISERROR(MATCH(B2,E2:E30,0)),IF(ISERROR(MATCH(B2,F2:F30,0)),"No color","Pink"),"Yellow")

You could then use the result of cell B4 for the VLOOKUP in cell B6.

The added advantage is that you wouldn't need that many conditional formatting rules: you can simply compare the color in B4 and assign a conditional formatting rule accordingly in B2. It's way more maintainable and less error-prone.

1
votes

Using Named Ranges for conditional formatting (no script, no helper cell)

Since Francesco's excellent proposition on using formulas instead of a script is your choice, you could also try the following.

  • Turn your team ranges to Named Ranges (by going to Data>>Named Ranges from the top menu) and name them e.g. yellow and PINK

  • Apply conditional formatting to your data validated cells (B3:E3) by using these two formulas (under Custom formula is found at the end of Conditional formatting rules): 

=REGEXMATCH(B3,""&JOIN("|",FILTER(INDIRECT("yellow"),INDIRECT("yellow")<>""))&"")
and 

=REGEXMATCH(B3,""&JOIN("|",FILTER(INDIRECT("PINK"),INDIRECT("PINK")<>""))&"")

(Make sure you adjust your ranges according to your data)

Advantages:

  1. It is your original cells that get color coded.

  2. Named Ranges can be placed anywhere, on any tab and can grow to your needs.

  3. You do not need the extra helper cell.

Using <code>Named Ranges</code> for conditional formatting

Make a note:

  • We must use INDIRECT when using named ranges with conditional formatting for the function to work.
  • We must also surround the named range with double quotes "". So, to refer to the named range yellow we use INDIRECT("yellow).