[FIXED] How to change a cells colour based on a cell on a seperate sheet being empty

Issue

I'm struggling to nail this. I'm trying to use conditional formatting on google sheets to change the colour of a group of cells based on a cell in a different sheet being empty.

So for example I have a sheet called "Controls" with an empty cell, M13, to write a persons name. Once that cell has had text inputted I would like a group of cells, BE2:BN25, to change colour to white so as effectively 'unlock' them for data entry.

I've tried =NOT(ISBLANK("Controls!M13")) but it's not changing colour once some text has been entered to that cell.

Is this possible on google sheets or am I just making a glaringly obvious error?

Thanks in advance for your help.

Solution

try:

=NOT(ISBLANK(INDIRECT("Controls!M13")))

Answered By - player0

Answer Checked By - David Goodson (PHPFixing Volunteer)

Read More

[FIXED] how to apply conditional formatting to a named range?

Issue

I'm searching for a way in google sheets to use a named range, or preferably by referring to a cell using INDIRECT() to specify the range to which the formatting should be conditionally applied.

My approach has been to create a formula in cell A1 which specifies a range, say f1:k10, but which can change dynamically. When I enter =indirect(A1) in the box "Apply to range" then I get the error message "Please enter a valid range"

This tells me that Google Sheets doesn't allow named or indirect ranges as the target range which I want to format. I'm hoping that I'm wrong and that there is a nice simple solution?...

I've tried:

=indirect(a1)
=indirect ($a$1)
indirect(a1)
=range-name
range-name

I've also spent a couple of hours trawling google for an answer

Ideally, when I specify a named range or refer indirectly to a range then I would expect that the specified range will be formatted in accordance with the criteria that I am able to set up in the format rules.

Solution

nope, you are right, Google Sheets does not support referenced ranges in Apply to range field.

kind of same as here:
Is it possible to assign conditional formatting to a named range in Google Sheets?

Answered By - player0

Answer Checked By - Candace Johnson (PHPFixing Volunteer)

Read More