Google Sheets – Strikethrough Name in Cell if Matches Another Range

conditional formattingformulasgoogle sheets

So we've got a calendar on google sheets, it has a bunch of different things besides just the days but the part I'm working on is easily displaying who is not going to be working that day.

We have a list of people who are being scheduled for things during the day, and they are in different places in a range almost every day so this solution needs to be flexible enough to deal with that.

What I am trying to accomplish is to be able to input a name and reason in the section at the bottom, and to have the actual schedule part automatically strikethrough any name that has been input into the bottom section.

I've made an example Sheet where you can see the input range is C116:J125 and the range I would like names to be struckthrough is D4:D73.

I've searched as much as I can but I'm either not experienced enough or simply cannot find a solution for this. I've found many answer to things that are almost fixes, but as you all probably know Sheets is rather specific and none of those answer have actually worked. Thanks in advance!

Best Answer

@Steven, I've added a sheet ("Erik Help") with the following custom CF formula/rule applied to D4:D73 ...

=NOT(ISERROR(VLOOKUP("*"&D4&"*",{C$116:C$125;E$116:E$125},1,FALSE)))

NOTES:

NOT(ISERROR(VLOOKUP(...))) basically means that something was found (i.e., if it's not an error when Sheets tries to find it, then it was found).

The "*" appended to the front and back of D4 are wildcards. This means "whatever is in D4, even if there is other stuff before it or after it."

Between the curly brackets {...}, I've just stacked your two bottom ranges where you list people who are out. That's the range that the VLOOKUP will search.

...1, FALSE) means "search column 1" (because, when stacked, there is only one virtual column in memory) and that the items in that column are in no particular order (i.e., ordered data = FALSE).

Method 1: String comparison with >= and <

The query string

select A where A < 'G'

selects all strings that would precede G in a dictionary, i.e., all that begin with letters A-F. This is case-sensitive. The case-insensitive form is

select A where lower(A) < 'g'

More complex examples

select A where lower(A)<'cp'

selects in the range a-co.

select A where lower(A) >= 'cp' and lower(A) < 'hb'

selects the range cp-ha.

Method 2: regular expressions

The query string

where B matches '^[A-F].*'

selects the rows where the content of B begins with a letter from A to F. (The regular expression means: ^ beginning of line, [A-F] one character in this range, .* other characters may follow.)

The match is case-sensitive. Its case-insensitive version would be

where lower(B) matches '^[a-f].*'

More complex examples

Matching strings in alphabetic range "a" to "co".

where lower(B) matches '^([ab]|c[a-o]).*'

Here | separates alternatives. Beginning with a or b is okay. So is beginning with c, but only if followed by a letter in range a-o.

Matching the range cp-ha:

where lower(B) matches '^(c[p-z]|[d-g]|ha).*'

There are three alternatives here: c followed by p-z, anything beginning with d-g , and ha.

Google Sheets – How to Use IMPORTRANGE with a Variable Range-String

If your referring to the cell where the regular text is you don't need to add the additional "" around the ="""2016 Data!"&B41&"2:"&B41&"13"""

just simply enter: ="2016 Data!"&B41&"2:"&B41&"13"

You only add the additional quotes when your directly entering that data into the formula, not when its via the cell reference

Best Answer

Related Solutions

Google Sheets – QUERY Data Based on Alpha Range

Method 1: String comparison with >= and <

More complex examples

Method 2: regular expressions

More complex examples

Google Sheets – How to Use IMPORTRANGE with a Variable Range-String

Related Topic