Conditional Formatting - reference a table dynamically

T

tana

New Member
Joined
Jan 22, 2021
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Hi,

Can I have some help on inputting a dynamic formula in conditional formatting?

I am working on a personalized calendar with some conditional formatting. One of conditional formatting is to reference a table in another tab with some dates. I used a formula to determine which cells to format [=COUNTIF(Dates!$B$5:$B$20,B7)]. I would like to replace Dates!$B$5:$B$20 with some dynamic referencing to the table in a tab called "Dates".

Thanks,
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
I would like to change the formula in conditional formatting so when a new date is added to Statutory holiday table, it will automatically updated. For example, if I add a new date Jan 1, 2026 as new year day in the table, the date will be auto formatted in 2026 calendar.

Calendar table that I have completed.

2025 Calendar Anna T.xlsx
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
32025
4
5JanuaryFebruaryMarchApril
6SMTWTFSSMTWTFSSMTWTFSSMTWTFS
7293031123426272829303112324252627281303112345
8567891011234567823456786789101112
9121314151617189101112131415910111213141513141516171819
1019202122232425161718192021221617181920212220212223242526
11262728293031123242526272812324252627282927282930123
122345678234567830311234545678910
13
14MayJuneJulyAugust
15SMTWTFSSMTWTFSSMTWTFSSMTWTFS
16272829301231234567293012345272829303112
174567891089101112131467891011123456789
1811121314151617151617181920211314151617181910111213141516
1918192021222324222324252627282021222324252617181920212223
202526272829303129301234527282930311224252627282930
2112345676789101112345678931123456
22
23SeptemberOctoberNovemberDecember
24SMTWTFSSMTWTFSSMTWTFSSMTWTFS
25311234562829301234262728293031130123456
2678910111213567891011234567878910111213
271415161718192012131415161718910111213141514151617181920
2821222324252627192021222324251617181920212221222324252627
29282930123426272829303112324252627282928293031123
3056789101123456783012345645678910
31
32Pay DayStatutory holiday
Calendar
Cell Formulas
RangeFormula
B7:H12,Z25:AF30,R25:X30,J25:P30,B25:H30,Z16:AF21,R16:X21,J16:P21,B16:H21,Z7:AF12,R7:X12,J7:P12B7=SEQUENCE(6,7,DATEVALUE(B5&$AA$3)-WEEKDAY(DATEVALUE(B5&$AA$3))+1)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Z25:AF30Expression=MONTH(Z25)<>MONTH($Z$23&1)textNO
R25:X30Expression=MONTH(R25)<>MONTH($R$23&1)textNO
J25:P30Expression=MONTH(J25)<>MONTH($J$23&1)textNO
B25:H30Expression=MONTH(B25)<>MONTH($B$23&1)textNO
Z16:AF21Expression=MONTH(Z16)<>MONTH($Z$14&1)textNO
R16:X21Expression=MONTH(R16)<>MONTH($R$14&1)textNO
J16:P21Expression=MONTH(J16)<>MONTH($J$14&1)textNO
B16:H21Expression=MONTH(B16)<>MONTH($B$14&1)textNO
Z7:AF12Expression=MONTH(Z7)<>MONTH($Z$5&1)textNO
R7:X12Expression=MONTH(R7)<>MONTH($R$5&1)textNO
J7:P12Expression=MONTH(J7)<>MONTH($J$5&1)textNO
B7:H12Expression=MONTH(B7)<>MONTH($B$5&1)textNO
B7:AF13,B15:AF22,B24:AF30Expression=COUNTIF(Dates!$I$5:$I$30,B7)textNO
B7:AF13,B15:AF22,B24:AF30Expression=COUNTIF(Dates!$B$5:$B$20,B7)textNO


And two reference tables to support the calendar

Statutory holiday Pay Day
DateDayMonthHolidayDateDayMonthHoliday
Jan 01, 202511New Year's DayJan 09, 202591Pay #1
Feb 17, 2025172Alberta Family DayJan 23, 2025231Pay #2
Apr 18, 2025184Good FridayFeb 06, 202562Pay #3
Apr 21, 2025214Easter Sunday ObservedSep 20, 1907209Pay #4
May 19, 2025195Victoria DayMar 06, 202563Pay #5
Jul 01, 202517Canada DayMar 20, 2025203Pay #6
Aug 04, 202548Heritage DayApr 03, 202534Pay #7
Sep 01, 202519Labour DayApr 17, 2025174Pay #8
Sep 30, 2025309Truth and Reconciliation DayMay 01, 202515Pay #9
Oct 13, 20251310Thanksgiving DayMay 15, 2025155Pay #10
Nov 11, 20251111Remembrance DayMay 29, 2025295Pay #11
Dec 25, 20252512Christmas DayJun 12, 2025126Pay #12
Dec 26, 20252612Boxing DayJun 26, 2025266Pay #13
Dec 29, 20252912Christmas Shut DownJul 10, 2025107Pay #14
Dec 30, 20253012Christmas Shut DownJul 24, 2025247Pay #15
Dec 31, 20253112Christmas Shut DownAug 07, 202578Pay #16
Aug 21, 2025218Pay #17
Sep 04, 202549Pay #18
Sep 18, 2025189Pay #19
Oct 02, 2025210Pay #20
Oct 16, 20251610Pay #21
Oct 30, 20253010Pay #22
Nov 13, 20251311Pay #23
Nov 27, 20252711Pay #24
Dec 11, 20251112Pay #25
Dec 24, 20252412Pay #26


Thanks,
 
Last edited:
Upvote 0
tana said:
Hi,

Can I have some help on inputting a dynamic formula in conditional formatting?

I am working on a personalized calendar with some conditional formatting. One of conditional formatting is to reference a table in another tab with some dates. I used a formula to determine which cells to format [=COUNTIF(Dates!$B$5:$B$20,B7)]. I would like to replace Dates!$B$5:$B$20 with some dynamic referencing to the table in a tab called "Dates".

Thanks,
Click to expand...
If you're Holiday or Pay Date tables are actually formatted as Excel Tables, then you can reference them this way:
Excel Formula: 
=COUNTIF(t_Holidays[Date],B7)
t_Holidays = Table Name
[Date] = Column Header
1736065165449.png

Use your own naming convention. You don't need the Sheet Name when referencing a Table Range as opposed to a Standard Range.
The added benefit is that as your table changes in size, you don't need to update your formula; it's automatic.

Using structured references with Excel tables - Microsoft Support

Structured references make it easier to use formulas with Excel tables by replacing cell references, such as C2:C7, with predefined names for the items in a table.
support.microsoft.com support.microsoft.com
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

I
  • Question Question
Trace Dependents - Conditional Formatting
Replies
3
Views
158
ionelz
I
J
  • Question Question
Help with Sumifs formula involving dates
Replies
5
Views
152
Joe4
Joe4
datatronics505
  • Solved
Absolute reference to a cell of another table
Replies
5
Views
285
datatronics505
datatronics505
D
  • Solved
Conditional Formatting Using VBA
2
Replies
15
Views
530
DanteAmor
DanteAmor
L
  • Solved
Formatting cells filled by array formulae
Replies
5
Views
290
Ludwig
L

Forum statistics

Threads
1,225,367
Messages
6,184,542
Members
453,241
Latest member
rahuldev31

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top