Dynamic Named ranges in formula.

[Phil Payne]

Hello all,

I have set up the means to determine and count the number of public holidays during a year on a month by month basis.
They work but I need the calendars to be dynamic to allow local holidays to be added.
I have tried to set up a dynamic ranges to work within the existing formula to no avail, an error always occurs.

Can someone tell the what adjustments are needed to this working formula to make it work with dynamic range please?
=NETWORKDAYS(P2,EOMONTH(P2,0),Holiday_Calendars!$E$4:$E$13)

I would like to replace “Holiday_Calendars!$E$4:$E$13” part with a dynamic named range.

Any help is appreciated.

Holiday_Calendars and Data Sheet images attached for info:

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

What is the name of your dynamic named range & how is it defined?

 

[Phil Payne]

My dynamic named range is not dynamic. I have problems creating it.
I have a named range only and even that won't work when combined with the rest of the formula.
My named Range is "Hols2022". I hope you can see the image OK.

I've taken your point about my Account Details - thanks will do.

For speed I am using MS 365 Apps for Enterprise

 

[Fluff]

Are the values in the Hols2022 range actual dates or text? As the formula should work quite happily with a named range.

 

[Phil Payne]

Well,

I tried a basic named range again "=NETWORKDAYS(M2,EOMONTH(M2,0),Hols2022)" and it worked.

So moving on I tried "=NETWORKDAYS(P2,EOMONTH(P2,0),Hols2023)" which did not work!!! Returns a #VALUE! error???

Formatted cell in Data Sheet presents long date 01 December 2022 (formula bar shows 01/01/2022)
The named range cell presents in exactly the same way!

 

[Fluff]

Is Hols2023 a dynamic range?

 

[Phil Payne]

No it is not. I'm trying to get the basics right first.

FYI When I google I get this answer time and again:-
"To reference a cell or range of cells in another worksheet in the same workbook, put the worksheet name followed by an exclamation mark (!) before the cell address. For example, to refer to cell A1 in Sheet2, you type Sheet2!A1. For example, to refer to cells A1:A10 in Sheet2, you type Sheet2!A1:A10".

When I do that for the named range it insists on changing the worksheet name for the workbook(.xlsm) name md fails??

 

[Fluff]

As those named ranges are workbook scope, you do not put the sheet name infront of them, just use

=NETWORKDAYS(P2,EOMONTH(P2,0),Hols2022)

as you showed.

Although your named ranges for 2023 onwards look to conatin text which is the problem.

 

[Phil Payne]

Thank you Fluff,
You cracked the going forward issue for me.
I just need to make the ranges dynamic!
Can you help with that too?
Thanks.

 

[Fluff]

Will you ever have anything below the dates on the Holiday_calendars sheet?