Recently I had to change previously Workbook-scoped named ranges to Worksheet-scope. These were used in conditional formatting rules. Now I appear to be unable to continue using them by adding the sheets name to the reference:
Sheet1 contains data and uses conditional formatting. Suppose Sheet2 contains a named range "ConstantX" referring to Sheet2:A1. This Name is scoped to Sheet2.
When trying to set up a CF in Sheet1 as e.g. =Q3 > Sheet2!ConstantX I receive the error message "This type of reference cannot be used in a Conditional Formatting formula".
It is however happy if I were to replace Sheet2!ConstantX with Sheet2!A1, but this is not an acceptable workaround. Changing the scope back to workbook is equally bad unfortunately, since that particular sheet is copied and pasted when I run my automated updates, destroying workbook-scope references.
Is there a solution to be using worksheet-scoped named ranges in another worksheet's conditional formatting rules?
