Dynamic holiday range formula

[Retroshift]

Hi,

I have the following formula which is inserted on sheets through VBA code:

=SUMPRODUCT((B3:AF3="AD")*(WEEKDAY(B2:AF2,2)<6)*(ISNA(MATCH(B2:AF2,HolidaySheet!A2:A3,0))))

The A2:A3 range contains two holiday dates which are on a separate worksheet. The holidays change depending on the year entered in an inputbox.
Every year entered in the inputbox creates a new sheet for that year. The new sheet contains the formula above including the holiday range.

Now, when I create a sheet for the year 2023 for example, the formula consists the holiday range for that year (2023). But, when I create a new sheet for the year 2024, the holiday range refers to the year 2024 for both sheets 2023 and 2024. My question is: how to solve this issue by making the holiday range formula dynamic so that the newly created 2023 sheet contains the formula referring to the holidays of 2023, and the newly created 2024 sheet contains the formula referring to the holidays of 2024?

 

[Jeffrey Mahoney]

All my holiday tables have multiple years in them; one table for multiple years. Just keep a running list and then remove those you don't need in a few years. Make your Holiday list an Excel table and reference the table range.

 

[Retroshift]

All my holiday tables have multiple years in them; one table for multiple years. Just keep a running list and then remove those you don't need in a few years. Make your Holiday list an Excel table and reference the table range.

Hi, making tables for multiple years is no option as this would make the file too large.
Isn't there a way to create a user defined function that can modify the year of the holiday dates in the "A2:A3" range, by matching them to a reference cell that contains the relevant year?

 

[Jeffrey Mahoney]

I understand your concern for keeping your files smaller. The one clean way you reference a different set of holidays is to use the indirect function. You could give each year a named range, put that name in the column above your data, and then change that as needed.

Going back to holiday tables with multiple years. Most companies and people only celebrate around 7 to 10 holidays. At that rate, your table for ten years would only contain 100 rows at most.

Jeff