Please see calendar example here. I have created multiple conditional formatting so calendar updates with proper colors for each date depending on what section it falls under. My issue is - how do I make sure that conditional formatting updates when sheet name changes. Now I can only keep it saying Sheet1. Any help is appreciated!
How do I automatically update all conditional formatting / named ranges when I change my sheet name?
Asked
Active
Viewed 133 times
2
-
1@player0 it should be open now. Thanks for that catch. – ANu Mar 11 '19 at 19:41
1 Answers
0
that's a good question, however, you would be surprised to know that you don't need to change anything after you change sheet name. it will kind of remember the old state (eg. you are safe to rename your sheets anytime you want without losing conditional format rules):
however, the issue is that you are not supposed to reference a sheet name inside INDIRECT inside Conditional format rules. eg change:
=MATCH(K1,(Indirect("Sheet1!Cast")),0) to =MATCH(K1,(Indirect("Cast")),0).
Named Ranges gets auto updated with the new sheet name and also note that Named Ranges are not bound to an individual sheet. they are valid on all sheets.
player0
- 124,011
- 12
- 67
- 124
-
Yes it works for Holidays - But not all the other dates (to the right of the first page). Those are the dates that are constantly updated so it's preferable that it's on the same page. --- So when I update the Sheet1 name, alll the conditional formatting that is for Columns D:J goes away. So for example - if you input dates for Cast Start or Cast end. It works until you change sheet name. Once you change Sheet name, all the colors go away. Does that make sense? – ANu Mar 11 '19 at 21:36