Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it possible to assign conditional formatting to a named range in Google Sheets?

I'd like to apply a conditional formatting rule to a named range. Is that even possible? How do I do that? When trying to enter the Name of the Range to the Field where you set up the range the rule applies to it won't accept my input.

Also with INDIRECT it does not work:

enter image description here

like image 675
haemse Avatar asked Apr 01 '19 14:04

haemse


People also ask

Can you apply conditional formatting to a named range?

For custom formulas in Sheets conditional formatting, the formula usually accesses a column value in the first row of the format range. Named range values can be used in custom formulas using INDIRECT function.

Can I use named range in conditional formatting Google Sheets?

By design, G-Sheets Conditional Formatting does not allow to reference a range on another sheet as part of a Custom Formula condition. The workaround is to use a Named Range.

How do you set conditional formatting for a range of values?

Select one or more cells in a range, table, or PivotTable report. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Top/Bottom Rules. Select the command you want, such as Top 10 items or Bottom 10 %. Enter the values you want to use, and then select a format.


3 Answers

enter image description here

this is not possible in Google Sheets


for the custom formula you need to wrap it into INDIRECT formula like:

0

like image 149
player0 Avatar answered Oct 21 '22 20:10

player0


I think it is not possible, it is not documented in official docs.

Range names:

  • Can contain only letters, numbers, and underscores.
  • Can't start with a number, or the words "true" or "false."
  • Can't contain any spaces or punctuation.
  • Must be 1–250 characters.
  • Can't be in either A1 or R1C1 syntax. For example, you might get an error if you give your range a name like "A1:B2" or "R1C1:R2C2."
like image 27
PEZO Avatar answered Oct 21 '22 21:10

PEZO


The following works for my specific use case, where the named range is an "unknown" number of rows.

In this case, A1 is a column heading, and is not part of the named range. A2:A5 is currently assigned to the named range, CitationType. Conditional formatting is applied to A1:A based on the formula:

=and(row(A1)>1,row(A1)<=1+rows(indirect("CitationType")))

I did not find an easy way (without scripting) to get the address details of a named range, allowing for "arbitrary" usage in conditional formatting.

like image 1
AStephens Avatar answered Oct 21 '22 19:10

AStephens