Conditional formatting 'Named Range' dilemma


B

Blue Max

We have created a series of named ranges which were defined using the
OFFSET() function. The purpose of this was to define ranges that would
always properly adjust when new rows or columns were inserted into the table
with the cursor position on the top row or left column of the table (these
positions normally add rows or columns outside the specified range - i.e.,
range does not adjust in all formulas).

This works well, until we try to use defined names with the 'Conditional
Formatting' feature. If we use a named range to specify the range for a
conditional formatting rule, the named range is converted to the actual
resulting range. The problem here is that the range used for the
conditional formatting is no longer dynamic. It is a fixed range that no
longer matches the named range if it is changed in the ways described above.

We suspect that this treatment may also be evident elsewhere in the program.
Does anyone know why conditional formatting rules do not retain the range as
the defined 'Range Name' originally specified by the user? Likewise, does
anyone know how to force the rule to retain the name versus the range, so
that it will dynamically change when needed?
 
Ad

Advertisements

C

Charles Williams

Works fine for me: I created a dynamic name range called Fred, inserted a
conditional format formula =SUM(Fred)>200
and when I extended the range so that the sume became greater than 200 the
conditional format was triggered.

Charles
___________________________________
London Excel Users Conference April 1-2
The Excel Calculation Site
http://www.decisionmodels.com
 
B

Blue Max

Hello Charles,

Thank you for the reply. Given your answer, however, it appears that I may
have communicated our question poorly. I was referring to a range specified
in the 'Applies To' field of the 'Conditional Formatting Rules Manager'
dialog versus the actual formula for the Conditional Format. This field
designates which cells the conditional formatting rule currently apples to.
It is in the 'Applies To' field where we seem to have the problem of Excel
converting the named ranges to simple range references; thereby losing the
dynamic features of the named range. Does this help clarify the issue?

Thanks,
Richard

****************
 
C

Charles Williams

I don't think thats going to work:

Looking at the object model you can see that Conditional Formats are a
property of the Range Object.
A Range object cannot have its address defined by a formula, it needs to be
a reference.

Dynamic named ranges are actually Named Formulae, (all Named Ranges are
really Named Array Formulae).

Charles
___________________________________
London Excel Users Conference April 1-2
The Excel Calculation Site
http://www.decisionmodels.com
 
B

Blue Max

Thanks, Charles. I think I am following your thinking, but am disappointed
that the named ranges are not valid references for the conditional
formatting rules. This means that my conditional format references will
likely have to be manually changed each time the range has a row added to
the top or left of the database, because the addition will fall outside the
current range with the OFFSET() function specified in the named range.

Thanks,
Richard

****************
 
Ad

Advertisements

Joined
Nov 21, 2011
Messages
1
Reaction score
0
I came across this posting because I was having the same problem...but... not on all my CF 'Applies to' ranges. After finding no solutions, I started looking into why some CF ranges were working but not others. It turns out that only ranges that were using an absolute reference in the Rule Formula were getting fragmented. If only relative addressing is used in the Rule Formula, then no fragmenting will occur.

Example: =$A7 <> $A6 is a rule I use to underline all rows in a table where the value in the first column is different between the current row and the next row. This produces fragmenting of the 'Applies to' ranges when a row is inserted.

TO Fix, change the formula to using relative references:
= OFFSET(A6, 1, 1 - COLUMN(A6) ) <> OFFSET(A6, 0, 1 - COLUMN(A6) )

The "1-COLUMN(A6)" points back to first column no matter which cell is being 'tested' so in effect replaces the absolute '$' reference.
The first OFFSET function returns the value same as $A7 by offsetting 1 row down from the base A6. The second OFFSET does not change the row, but only returns the A6 cell value. Combined this keeps the formula as a relative formula, which Excel CF seems to handle as desired.

Hope this helps anyone else coming across the Conditional formatting 'Named Range' dilemma.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top