I created a yearly calendar sheet that would automatically highlight dates that were put into a variety of tables through conditional formatting. For example, if Table1 contained the dates 1/1/2023, then the cell containing that date would be highlighted in blue. There are numerous tables with different colors for highlighting.
The issue arrives when I copy the sheet to work on another year. In this case, my tables are copied but the table names are slightly changed (with additional numbers at the end) which means that the conditional formatting no longer operates correctly because it references the previous names, which no longer exist.
Is there a way to create a dynamic reference in conditional formatting such that when I copy the sheet, the conditional formatting works correctly even with the changed table names? Excel won't allow tables with the same name to exist even if they're on separate sheets.
Thanks!
