Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting 'Named Range' dilemma
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting 'Named Range' dilemma
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 "Blue Max" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting 'Named Range' dilemma
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 **************** "Charles Williams" wrote in message ... 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 "Blue Max" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting 'Named Range' dilemma
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 "Blue Max" wrote in message ... 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 **************** "Charles Williams" wrote in message ... 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 "Blue Max" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting 'Named Range' dilemma
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 **************** "Charles Williams" wrote in message ... 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 "Blue Max" wrote in message ... 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 **************** "Charles Williams" wrote in message ... 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 "Blue Max" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting - 2 Worksheets one Named Range | Excel Worksheet Functions | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Conditional formatting if value in cell is found in a named range | Excel Worksheet Functions | |||
Conditional formatting on named text field | Excel Discussion (Misc queries) | |||
HOW TO USE CONDITIONAL FORMATTING FROM ONE RANGE TO ANOTHER RANGE | Excel Worksheet Functions |