Dynamic Address for Use in Function Always returns ERROR

K

KhmerBoi1

New Member
Joined
Aug 12, 2014
Messages
30
I'm trying to create a Federal Employee Calendar. I've historical dataset of the Federal Holidays since 2017 going till the year 2030 as shown below:

Calendar.1.xlsx
ABCDEFGHIJKLMNO
1Federal Holidays by Year20172018201920202021202220232024202520262027202820292030
2New Year’s Day02-Jan-201701-Jan-201801-Jan-201901-Jan-202001-Jan-202131-Dec-202102-Jan-202301-Jan-202401-Jan-202501-Jan-202601-Jan-202731-Dec-202701-Jan-202901-Jan-2030
3Birthday of Martin Luther King, Jr.16-Jan-201715-Jan-201821-Jan-201920-Jan-202018-Jan-202117-Jan-202216-Jan-202315-Jan-202420-Jan-202519-Jan-202618-Jan-202717-Jan-202815-Jan-202921-Jan-2030
4Washington’s Birthday20-Feb-201719-Feb-201818-Feb-201917-Feb-202015-Feb-202121-Feb-202220-Feb-202319-Feb-202417-Feb-202516-Feb-202615-Feb-202721-Feb-202819-Feb-202918-Feb-2030
5Memorial Day29-May-201728-May-201827-May-201925-May-202031-May-202130-May-202229-May-202327-May-202426-May-202525-May-202631-May-202729-May-202828-May-202927-May-2030
6Juneteenth National Independence Day19-Jun-201719-Jun-201819-Jun-201919-Jun-202018-Jun-202120-Jun-202219-Jun-202319-Jun-202419-Jun-202519-Jun-202618-Jun-202719-Jun-202819-Jun-202919-Jun-2030
7Independence Day04-Jul-201704-Jul-201804-Jul-201903-Jul-202005-Jul-202104-Jul-202204-Jul-202304-Jul-202404-Jul-202503-Jul-202605-Jul-202704-Jul-202804-Jul-202904-Jul-2030
8Labor Day04-Sep-201703-Sep-201802-Sep-201907-Sep-202006-Sep-202105-Sep-202204-Sep-202302-Sep-202401-Sep-202507-Sep-202606-Sep-202704-Sep-202803-Sep-202902-Sep-2030
9Columbus Day09-Oct-201708-Oct-201814-Oct-201912-Oct-202011-Oct-202110-Oct-202209-Oct-202314-Oct-202413-Oct-202512-Oct-202611-Oct-202709-Oct-202808-Oct-202914-Oct-2030
10Veterans Day10-Nov-201712-Nov-201811-Nov-201911-Nov-202011-Nov-202111-Nov-202210-Nov-202311-Nov-202411-Nov-202511-Nov-202611-Nov-202710-Nov-202812-Nov-202911-Nov-2030
11Thanksgiving Day23-Nov-201722-Nov-201828-Nov-201926-Nov-202025-Nov-202124-Nov-202223-Nov-202328-Nov-202427-Nov-202526-Nov-202625-Nov-202723-Nov-202822-Nov-202928-Nov-2030
12Christmas Day25-Dec-201725-Dec-201825-Dec-201925-Dec-202024-Dec-202126-Dec-202225-Dec-202325-Dec-202425-Dec-202525-Dec-202624-Dec-202725-Dec-202825-Dec-202925-Dec-2030
HOLIDAYS
Cell Formulas
RangeFormula
B2:O2B2=DATE(B$1,1,1)+CHOOSE(WEEKDAY(DATE(B$1,1,1)),1,0,0,0,0,0,-1)
B3:O3B3=DATE(B$1,1,(1+7*3)-WEEKDAY(DATE(B$1,1,8-2)))
B4:O4B4=DATE(B$1,2,(1+7*3)-WEEKDAY(DATE(B$1,2,8-2)))
B5:O5B5=DATE(B$1,6,1)-WEEKDAY(DATE(B$1,6,6))
B6:O6B6=DATE(B$1,6,19)+CHOOSE(WEEKDAY(DATE(B$1,6,19)),1,0,0,0,0,0,-1)
B7:O7B7=DATE(B$1,7,4)+CHOOSE(WEEKDAY(DATE(B$1,7,4)),1,0,0,0,0,0,-1)
B8:O8B8=DATE(B$1,9,(1+7*1)-WEEKDAY(DATE(B$1,9,8-2)))
B9:O9B9=DATE(B$1,10,(1+7*2)-WEEKDAY(DATE(B$1,10,8-2)))
B10:O10B10=DATE(B$1,11,11)+CHOOSE(WEEKDAY(DATE(B$1,11,11)),1,0,0,0,0,0,-1)
B11:O11B11=DATE(B$1,11,(1+7*4)-WEEKDAY(DATE(B$1,11,8+2)))
B12:O12B12=DATE(B$1,12,25)+CHOOSE(WEEKDAY(DATE(B$1,12,25)),1,0,0,0,0,0,-1)
Named Ranges
NameRefers ToCells
HolidayRange=OFFSET(INDIRECT(ADDRESS(1, MATCH(CurrentYear, Years,0),1,1)),0,0,COUNTA(HOLIDAYS!$A:$A))I2:I12


I would like to use Conditional Formatting to highlight the Holidays with a RED fill color and WHITE font color in the below calendar:
Calendar.1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
12024
2
3JanuaryFebruaryMarch
4SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
5 010203040506    010203     0102
6070809101112130405060708091003040506070809
7141516171819201112131415161710111213141516
8212223242526271819202122232417181920212223
928293031   2526272829  24252627282930
10              31      
11
12AprilMayJune
13SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
14 010203040506   01020304      01
15070809101112130506070809101102030405060708
16141516171819201213141516171809101112131415
17212223242526271920212223242516171819202122
18282930    262728293031 23242526272829
19              30      
20
21JulyAugustSeptember
22SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
23 010203040506    01020301020304050607
24070809101112130405060708091008091011121314
25141516171819201112131415161715161718192021
26212223242526271819202122232422232425262728
2728293031   252627282930312930     
28                     
29
30OctoberNovemberDecember
31SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
32  0102030405     010201020304050607
33060708091011120304050607080908091011121314
34131415161718191011121314151615161718192021
35202122232425261718192021222322232425262728
362728293031  24252627282930293031    
37                     
CALENDAR_PORTRAIT
Cell Formulas
RangeFormula
A1A1=CurrentYear
B3B3=DATE(CurrentYear,1,1)
J3J3=DATE(CurrentYear,2,1)
R3R3=DATE(CurrentYear,3,1)
B5,R32,J32,B32,R23,J23,B23,R14,J14,B14,R5,J5B5=B3-(WEEKDAY(B3,1)-1)
C5:H10,S32:X37,K32:P37,C32:H37,S23:X28,K23:P28,C23:H28,S14:X19,K14:P19,C14:H19,S5:X10,K5:P10C5=B5+1
B6:B10,R33:R37,J33:J37,B33:B37,R24:R28,J24:J28,B24:B28,R15:R19,J15:J19,B15:B19,R6:R10,J6:J10B6=B5+7
B12B12=DATE(CurrentYear,4,1)
J12J12=DATE(CurrentYear,5,1)
R12R12=DATE(CurrentYear,6,1)
B21B21=DATE(CurrentYear,7,1)
J21J21=DATE(CurrentYear,8,1)
R21R21=DATE(CurrentYear,9,1)
B30B30=DATE(CurrentYear,10,1)
J30J30=DATE(CurrentYear,11,1)
R30R30=DATE(CurrentYear,12,1)
Named Ranges
NameRefers ToCells
CurrentYear=SETTINGS!$C$5B3:H3, B12:H12, B21:H21, B30:H30, J3:P3, J12:P12, J21:P21, J30:P30, R3:X3, R12:X12, R21:X21, R30:X30
Cells with Conditional Formatting
CellConditionCell FormatStop If True
R5:X10Expression=MONTH(R5)<>MONTH($R$3)textYES
B5:H10Expression=MONTH(B5)<>MONTH($B$3)textYES
R32:X37Expression=MONTH(R32)<>MONTH($R$30)textYES
J32:P37Expression=MONTH(J32)<>MONTH($J$30)textYES
B32:H37Expression=MONTH(B32)<>MONTH($B$30)textYES
R23:X28Expression=MONTH(R23)<>MONTH($R$21)textYES
J23:P28Expression=MONTH(J23)<>MONTH($J$21)textYES
B23:H28Expression=MONTH(B23)<>MONTH($B$21)textYES
R14:X19Expression=MONTH(R14)<>MONTH($R$12)textYES
J14:P19Expression=MONTH(J14)<>MONTH($J$12)textYES
B14:H19Expression=MONTH(B14)<>MONTH($B$12)textYES
J5:P10Expression=MONTH(J5)<>MONTH($J$3)textYES
B5:H10,J5:P10,R5:X10,B14:H19,J14:P19,R14:X19,B23:H28,J23:P28,R23:X28,B32:H38,J32:P38,R32:X38Expression=OR(WEEKDAY(B5)=1, WEEKDAY(B5)=7)textNO
G:G,O:O,W:WExpression=ISNUMBER(MATCH(G1,AltFriday,0))textNO


I was trying to use a the following formula to find the Holidays that are in the below named range although it returns an error:
Calendar.1.xlsx
AE
9FALSE
CALENDAR_PORTRAIT
Cell Formulas
RangeFormula
AE9AE9=ISNUMBER(MATCH(C5,HolidayRange,0))

Named Ranges
NameRefers ToCells
HolidayRange=OFFSET(INDIRECT(ADDRESS(1, MATCH(CurrentYear, Years,0),1,1)),0,0,COUNTA(HOLIDAYS!$A:$A))I2:I12


I know that I'm formatting the dates in a specific way and if I format the dates as
Excel Formula: 
GENERAL
then the formula will evalute to
Excel Formula: 
TRUE
I would like to write the named range so that it is dynamic so that if I increment the year in either direction which changes the value of
Named Ranges
NameRefers ToCells
CurrentYear=SETTINGS!$C$5B3:H3, B12:H12, B21:H21, B30:H30, J3:P3, J12:P12, J21:P21, J30:P30, R3:X3, R12:X12, R21:X21, R30:X30

Calendar.1.xlsx
ABCD
1
2Settings
3
4
5Year:2024
6
SETTINGS

It will adjust the dynamic range and get the correct years dates.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
I think you're making this more complicated than it has to be. Since both the days on the calendar, and the dates on the Holiday sheet both have a full date, you can just search the whole range for a match. Like this:

Book1
ABCDEFGHIJKLMNOPQRSTUVWXY
12024
2
31/1/20242/1/20243/1/2024
4SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
512/31/20231/1/20241/2/20241/3/20241/4/20241/5/20241/6/20241/28/20241/29/20241/30/20241/31/20242/1/20242/2/20242/3/20242/25/20242/26/20242/27/20242/28/20242/29/20243/1/20243/2/2024
61/7/20241/8/20241/9/20241/10/20241/11/20241/12/20241/13/20242/4/20242/5/20242/6/20242/7/20242/8/20242/9/20242/10/20243/3/20243/4/20243/5/20243/6/20243/7/20243/8/20243/9/2024
71/14/20241/15/20241/16/20241/17/20241/18/20241/19/20241/20/20242/11/20242/12/20242/13/20242/14/20242/15/20242/16/20242/17/20243/10/20243/11/20243/12/20243/13/20243/14/20243/15/20243/16/2024
81/21/20241/22/20241/23/20241/24/20241/25/20241/26/20241/27/20242/18/20242/19/20242/20/20242/21/20242/22/20242/23/20242/24/20243/17/20243/18/20243/19/20243/20/20243/21/20243/22/20243/23/2024
91/28/20241/29/20241/30/20241/31/20242/1/20242/2/20242/3/20242/25/20242/26/20242/27/20242/28/20242/29/20243/1/20243/2/20243/24/20243/25/20243/26/20243/27/20243/28/20243/29/20243/30/2024
102/4/20242/5/20242/6/20242/7/20242/8/20242/9/20242/10/20243/3/20243/4/20243/5/20243/6/20243/7/20243/8/20243/9/20243/31/20244/1/20244/2/20244/3/20244/4/20244/5/20244/6/2024
11
124/1/20245/1/20246/1/2024
13SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
143/31/20244/1/20244/2/20244/3/20244/4/20244/5/20244/6/20244/28/20244/29/20244/30/20245/1/20245/2/20245/3/20245/4/20245/26/20245/27/20245/28/20245/29/20245/30/20245/31/20246/1/2024
154/7/20244/8/20244/9/20244/10/20244/11/20244/12/20244/13/20245/5/20245/6/20245/7/20245/8/20245/9/20245/10/20245/11/20246/2/20246/3/20246/4/20246/5/20246/6/20246/7/20246/8/2024
164/14/20244/15/20244/16/20244/17/20244/18/20244/19/20244/20/20245/12/20245/13/20245/14/20245/15/20245/16/20245/17/20245/18/20246/9/20246/10/20246/11/20246/12/20246/13/20246/14/20246/15/2024
174/21/20244/22/20244/23/20244/24/20244/25/20244/26/20244/27/20245/19/20245/20/20245/21/20245/22/20245/23/20245/24/20245/25/20246/16/20246/17/20246/18/20246/19/20246/20/20246/21/20246/22/2024
184/28/20244/29/20244/30/20245/1/20245/2/20245/3/20245/4/20245/26/20245/27/20245/28/20245/29/20245/30/20245/31/20246/1/20246/23/20246/24/20246/25/20246/26/20246/27/20246/28/20246/29/2024
195/5/20245/6/20245/7/20245/8/20245/9/20245/10/20245/11/20246/2/20246/3/20246/4/20246/5/20246/6/20246/7/20246/8/20246/30/20247/1/20247/2/20247/3/20247/4/20247/5/20247/6/2024
20
217/1/20248/1/20249/1/2024
22SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
236/30/20247/1/20247/2/20247/3/20247/4/20247/5/20247/6/20247/28/20247/29/20247/30/20247/31/20248/1/20248/2/20248/3/20249/1/20249/2/20249/3/20249/4/20249/5/20249/6/20249/7/2024
247/7/20247/8/20247/9/20247/10/20247/11/20247/12/20247/13/20248/4/20248/5/20248/6/20248/7/20248/8/20248/9/20248/10/20249/8/20249/9/20249/10/20249/11/20249/12/20249/13/20249/14/2024
257/14/20247/15/20247/16/20247/17/20247/18/20247/19/20247/20/20248/11/20248/12/20248/13/20248/14/20248/15/20248/16/20248/17/20249/15/20249/16/20249/17/20249/18/20249/19/20249/20/20249/21/2024
267/21/20247/22/20247/23/20247/24/20247/25/20247/26/20247/27/20248/18/20248/19/20248/20/20248/21/20248/22/20248/23/20248/24/20249/22/20249/23/20249/24/20249/25/20249/26/20249/27/20249/28/2024
277/28/20247/29/20247/30/20247/31/20248/1/20248/2/20248/3/20248/25/20248/26/20248/27/20248/28/20248/29/20248/30/20248/31/20249/29/20249/30/202410/1/202410/2/202410/3/202410/4/202410/5/2024
288/4/20248/5/20248/6/20248/7/20248/8/20248/9/20248/10/20249/1/20249/2/20249/3/20249/4/20249/5/20249/6/20249/7/202410/6/202410/7/202410/8/202410/9/202410/10/202410/11/202410/12/2024
29
3010/1/202411/1/202412/1/2024
31SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
329/29/20249/30/202410/1/202410/2/202410/3/202410/4/202410/5/202410/27/202410/28/202410/29/202410/30/202410/31/202411/1/202411/2/202412/1/202412/2/202412/3/202412/4/202412/5/202412/6/202412/7/2024
3310/6/202410/7/202410/8/202410/9/202410/10/202410/11/202410/12/202411/3/202411/4/202411/5/202411/6/202411/7/202411/8/202411/9/202412/8/202412/9/202412/10/202412/11/202412/12/202412/13/202412/14/2024
3410/13/202410/14/202410/15/202410/16/202410/17/202410/18/202410/19/202411/10/202411/11/202411/12/202411/13/202411/14/202411/15/202411/16/202412/15/202412/16/202412/17/202412/18/202412/19/202412/20/202412/21/2024
3510/20/202410/21/202410/22/202410/23/202410/24/202410/25/202410/26/202411/17/202411/18/202411/19/202411/20/202411/21/202411/22/202411/23/202412/22/202412/23/202412/24/202412/25/202412/26/202412/27/202412/28/2024
3610/27/202410/28/202410/29/202410/30/202410/31/202411/1/202411/2/202411/24/202411/25/202411/26/202411/27/202411/28/202411/29/202411/30/202412/29/202412/30/202412/31/20241/1/20251/2/20251/3/20251/4/2025
3711/3/202411/4/202411/5/202411/6/202411/7/202411/8/202411/9/202412/1/202412/2/202412/3/202412/4/202412/5/202412/6/202412/7/20241/5/20251/6/20251/7/20251/8/20251/9/20251/10/20251/11/2025
Calendar_Portrait
Cell Formulas
RangeFormula
A1A1=CurrentYear
B3B3=DATE(CurrentYear,1,1)
J3J3=DATE(CurrentYear,2,1)
R3R3=DATE(CurrentYear,3,1)
B5,R32,J32,B32,R23,J23,B23,R14,J14,B14,R5,J5B5=B3-(WEEKDAY(B3,1)-1)
C5:H10,S32:X37,K32:P37,C32:H37,S23:X28,K23:P28,C23:H28,S14:X19,K14:P19,C14:H19,S5:X10,K5:P10C5=B5+1
B6:B10,R33:R37,J33:J37,B33:B37,R24:R28,J24:J28,B24:B28,R15:R19,J15:J19,B15:B19,R6:R10,J6:J10B6=B5+7
B12B12=DATE(CurrentYear,4,1)
J12J12=DATE(CurrentYear,5,1)
R12R12=DATE(CurrentYear,6,1)
B21B21=DATE(CurrentYear,7,1)
J21J21=DATE(CurrentYear,8,1)
R21R21=DATE(CurrentYear,9,1)
B30B30=DATE(CurrentYear,10,1)
J30J30=DATE(CurrentYear,11,1)
R30R30=DATE(CurrentYear,12,1)
Named Ranges
NameRefers ToCells
CurrentYear=Settings!$C$5B3:H3, B12:H12, B21:H21, B30:H30, J3:P3, J12:P12, J21:P21, J30:P30, R3:X3, R12:X12, R21:X21, R30:X30
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B5:X37Expression=COUNTIF(Holidays!$B$2:$O$12,B5)textNO


Some of the other conditional formatting for hiding dates and such didn't get carried over somehow, but you can see the holidays are showing up in red, and the rule at the bottom. I also noticed that Memorial Day (5/27/2024) got highlighted in June also. This is easily handled. When you have the CF rule that hides cells from another month, make sure that comes first in the hierarchy, and check the Stop box .
 
Last edited:
Upvote 0
Eric W said:
I think you're making this more complicated than it has to be. Since both the days on the calendar, and the dates on the Holiday sheet both have a full date, you can just search the whole range for a match. Like this:

Book1
ABCDEFGHIJKLMNOPQRSTUVWXY
12024
2
31/1/20242/1/20243/1/2024
4SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
512/31/20231/1/20241/2/20241/3/20241/4/20241/5/20241/6/20241/28/20241/29/20241/30/20241/31/20242/1/20242/2/20242/3/20242/25/20242/26/20242/27/20242/28/20242/29/20243/1/20243/2/2024
61/7/20241/8/20241/9/20241/10/20241/11/20241/12/20241/13/20242/4/20242/5/20242/6/20242/7/20242/8/20242/9/20242/10/20243/3/20243/4/20243/5/20243/6/20243/7/20243/8/20243/9/2024
71/14/20241/15/20241/16/20241/17/20241/18/20241/19/20241/20/20242/11/20242/12/20242/13/20242/14/20242/15/20242/16/20242/17/20243/10/20243/11/20243/12/20243/13/20243/14/20243/15/20243/16/2024
81/21/20241/22/20241/23/20241/24/20241/25/20241/26/20241/27/20242/18/20242/19/20242/20/20242/21/20242/22/20242/23/20242/24/20243/17/20243/18/20243/19/20243/20/20243/21/20243/22/20243/23/2024
91/28/20241/29/20241/30/20241/31/20242/1/20242/2/20242/3/20242/25/20242/26/20242/27/20242/28/20242/29/20243/1/20243/2/20243/24/20243/25/20243/26/20243/27/20243/28/20243/29/20243/30/2024
102/4/20242/5/20242/6/20242/7/20242/8/20242/9/20242/10/20243/3/20243/4/20243/5/20243/6/20243/7/20243/8/20243/9/20243/31/20244/1/20244/2/20244/3/20244/4/20244/5/20244/6/2024
11
124/1/20245/1/20246/1/2024
13SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
143/31/20244/1/20244/2/20244/3/20244/4/20244/5/20244/6/20244/28/20244/29/20244/30/20245/1/20245/2/20245/3/20245/4/20245/26/20245/27/20245/28/20245/29/20245/30/20245/31/20246/1/2024
154/7/20244/8/20244/9/20244/10/20244/11/20244/12/20244/13/20245/5/20245/6/20245/7/20245/8/20245/9/20245/10/20245/11/20246/2/20246/3/20246/4/20246/5/20246/6/20246/7/20246/8/2024
164/14/20244/15/20244/16/20244/17/20244/18/20244/19/20244/20/20245/12/20245/13/20245/14/20245/15/20245/16/20245/17/20245/18/20246/9/20246/10/20246/11/20246/12/20246/13/20246/14/20246/15/2024
174/21/20244/22/20244/23/20244/24/20244/25/20244/26/20244/27/20245/19/20245/20/20245/21/20245/22/20245/23/20245/24/20245/25/20246/16/20246/17/20246/18/20246/19/20246/20/20246/21/20246/22/2024
184/28/20244/29/20244/30/20245/1/20245/2/20245/3/20245/4/20245/26/20245/27/20245/28/20245/29/20245/30/20245/31/20246/1/20246/23/20246/24/20246/25/20246/26/20246/27/20246/28/20246/29/2024
195/5/20245/6/20245/7/20245/8/20245/9/20245/10/20245/11/20246/2/20246/3/20246/4/20246/5/20246/6/20246/7/20246/8/20246/30/20247/1/20247/2/20247/3/20247/4/20247/5/20247/6/2024
20
217/1/20248/1/20249/1/2024
22SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
236/30/20247/1/20247/2/20247/3/20247/4/20247/5/20247/6/20247/28/20247/29/20247/30/20247/31/20248/1/20248/2/20248/3/20249/1/20249/2/20249/3/20249/4/20249/5/20249/6/20249/7/2024
247/7/20247/8/20247/9/20247/10/20247/11/20247/12/20247/13/20248/4/20248/5/20248/6/20248/7/20248/8/20248/9/20248/10/20249/8/20249/9/20249/10/20249/11/20249/12/20249/13/20249/14/2024
257/14/20247/15/20247/16/20247/17/20247/18/20247/19/20247/20/20248/11/20248/12/20248/13/20248/14/20248/15/20248/16/20248/17/20249/15/20249/16/20249/17/20249/18/20249/19/20249/20/20249/21/2024
267/21/20247/22/20247/23/20247/24/20247/25/20247/26/20247/27/20248/18/20248/19/20248/20/20248/21/20248/22/20248/23/20248/24/20249/22/20249/23/20249/24/20249/25/20249/26/20249/27/20249/28/2024
277/28/20247/29/20247/30/20247/31/20248/1/20248/2/20248/3/20248/25/20248/26/20248/27/20248/28/20248/29/20248/30/20248/31/20249/29/20249/30/202410/1/202410/2/202410/3/202410/4/202410/5/2024
288/4/20248/5/20248/6/20248/7/20248/8/20248/9/20248/10/20249/1/20249/2/20249/3/20249/4/20249/5/20249/6/20249/7/202410/6/202410/7/202410/8/202410/9/202410/10/202410/11/202410/12/2024
29
3010/1/202411/1/202412/1/2024
31SUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
329/29/20249/30/202410/1/202410/2/202410/3/202410/4/202410/5/202410/27/202410/28/202410/29/202410/30/202410/31/202411/1/202411/2/202412/1/202412/2/202412/3/202412/4/202412/5/202412/6/202412/7/2024
3310/6/202410/7/202410/8/202410/9/202410/10/202410/11/202410/12/202411/3/202411/4/202411/5/202411/6/202411/7/202411/8/202411/9/202412/8/202412/9/202412/10/202412/11/202412/12/202412/13/202412/14/2024
3410/13/202410/14/202410/15/202410/16/202410/17/202410/18/202410/19/202411/10/202411/11/202411/12/202411/13/202411/14/202411/15/202411/16/202412/15/202412/16/202412/17/202412/18/202412/19/202412/20/202412/21/2024
3510/20/202410/21/202410/22/202410/23/202410/24/202410/25/202410/26/202411/17/202411/18/202411/19/202411/20/202411/21/202411/22/202411/23/202412/22/202412/23/202412/24/202412/25/202412/26/202412/27/202412/28/2024
3610/27/202410/28/202410/29/202410/30/202410/31/202411/1/202411/2/202411/24/202411/25/202411/26/202411/27/202411/28/202411/29/202411/30/202412/29/202412/30/202412/31/20241/1/20251/2/20251/3/20251/4/2025
3711/3/202411/4/202411/5/202411/6/202411/7/202411/8/202411/9/202412/1/202412/2/202412/3/202412/4/202412/5/202412/6/202412/7/20241/5/20251/6/20251/7/20251/8/20251/9/20251/10/20251/11/2025
Calendar_Portrait
Cell Formulas
RangeFormula
A1A1=CurrentYear
B3B3=DATE(CurrentYear,1,1)
J3J3=DATE(CurrentYear,2,1)
R3R3=DATE(CurrentYear,3,1)
B5,R32,J32,B32,R23,J23,B23,R14,J14,B14,R5,J5B5=B3-(WEEKDAY(B3,1)-1)
C5:H10,S32:X37,K32:P37,C32:H37,S23:X28,K23:P28,C23:H28,S14:X19,K14:P19,C14:H19,S5:X10,K5:P10C5=B5+1
B6:B10,R33:R37,J33:J37,B33:B37,R24:R28,J24:J28,B24:B28,R15:R19,J15:J19,B15:B19,R6:R10,J6:J10B6=B5+7
B12B12=DATE(CurrentYear,4,1)
J12J12=DATE(CurrentYear,5,1)
R12R12=DATE(CurrentYear,6,1)
B21B21=DATE(CurrentYear,7,1)
J21J21=DATE(CurrentYear,8,1)
R21R21=DATE(CurrentYear,9,1)
B30B30=DATE(CurrentYear,10,1)
J30J30=DATE(CurrentYear,11,1)
R30R30=DATE(CurrentYear,12,1)
Named Ranges
NameRefers ToCells
CurrentYear=Settings!$C$5B3:H3, B12:H12, B21:H21, B30:H30, J3:P3, J12:P12, J21:P21, J30:P30, R3:X3, R12:X12, R21:X21, R30:X30
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B5:X37Expression=COUNTIF(Holidays!$B$2:$O$12,B5)textNO


Some of the other conditional formatting for hiding dates and such didn't get carried over somehow, but you can see the holidays are showing up in red, and the rule at the bottom. I also noticed that Memorial Day (5/27/2024) got highlighted in June also. This is easily handled. When you have the CF rule that hides cells from another month, make sure that comes first in the hierarchy, and check the Stop box .
Click to expand...
@Eric W: While I understand that I may be making it more complicated than I need.

Excel Formula: 
=COUNTIF(Holidays!$B$2:$O$12,B5)

The above formula ONLY accounts for the holidays which are currently on the HOLIDAYS sheet. If I should add more Years past Column O then the above formula would not account for the new columns But a dynamic range would account for the new columns and utilizing MATCH() I would be able to know which column corresponds to the current year.

The workbook I replicated this from had all the Holidays as I have formatted them up above but additionally had Years 2018-2030 going down in columns A and B as well. This seemed redundant to me.

This workbook was also using the formula which ONLY match in column B.

Excel Formula: 
=ISNUMBER(MATCH(B5,HOLIDAYS!B:B,0))

I was trying to use an INDEX AND MATCH to find the holiday in a column which could be any column starting at B based on the CurrentYear.
 
Upvote 0
You could easily just extend the range to include more columns to the right that you haven't filled yet. But if you still want to look at just one column, use this formula in Conditional Formatting:

Excel Formula: 
= MATCH(B5,OFFSET(Holidays!$B$2,0,CurrentYear-Holidays!$B$1,11),0)

You could add the ISNUMBER if you want, but CF considers any positive number as TRUE, and any error as FALSE, so it's not necessary.
 
Upvote 0
You must log in or register to reply here.

Similar threads

vinzent
  • Question
Formula to get the 4th workday before end of month
Replies
8
Views
147
Tetra201
Tetra201
S
  • Solved
lookup return from range of dates
Replies
4
Views
113
Peter_SSs
Peter_SSs
B
  • Question
Copying data from a year tab and pasting into month tabs
Replies
2
Views
187
B-radK
B
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
1K
phiero21
P
W
  • Solved
Usign the =sort function in a Dynamic Array to sort by Month
Replies
3
Views
218
Fluff
Fluff

Forum statistics

Threads
1,215,069
Messages
6,122,959
Members
449,096
Latest member
Anshu121

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