• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

conditional formatting from different work sheets in excel 2016

P

paramnayak

Member
dear all.
how can i use conditional formatting from different workbook in excel 2016.
I have tried using name range but i am getting this message
"You may not use references to otherwork books for conditional formatting criteria"
 
Last edited:
The error message says it all. You will need to replicate your CF rules in the new workbook. I don't believe named ranges can be used in CF rules, either.
 
Named ranges can't be directly referenced from another workbook.

Several workarounds:
1. Set up formula to pull info from another workbook. Then use that range as named range for the workbook.

2. Use VBA along with ADO to pull in values of named range in another workbook. Create named range from it. However, do note this method will not work on dynamic named ranges.

3. Set up PowerQuery connection and pull info. Note this method can only be used for sheet/table set up. Named range isn't recognized as table (so you may need to transform the data as needed in PQ).

Named ranges can be used in CF, at least in Excel 2016. However, do note, you'll lose ability to type in the formula once you use F3 to paste in the named range. You'll need to commit the change and then come back to edit. Or just type in the defined name.
 
You must log in or register to reply here.
Back
Top