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:

 

[Phil Payne]

Yes the calendars need to be adjusted for national and also local employer agreements by the user.

 

[Fluff]

In that case you are going to have problems getting a dynamic range to work. Normally you would use something like

=A4:INDEX(A:A,COUNT(A:A)+3)

but this will not work if you have more dates below A14 (based on the image in your op)

 

[Phil Payne]

I know what you mean.
I have arranged the data for each year side by side (from left to right) so specifying a whole column range is not a problem as there can be no overlap of data.

I thought i had cracked it when I made the calendars ranges into tables. It immediately worked as far as adding and subtracting entries, unfortunately the Custom Views that were well established around he same data no longer work - the Custom View button is disabled..

Do you know why that should be?

Back to the drawing board.

 

[Fluff]

I've never used Custom Views, so cannot help with that.

 

[Phil Payne]

Not a problem Fluff, I think I've cracked it.

A long while ago i realised that if you establish an empty boundary around a range and set the Named Range to include the bottom empty boundary AND insist that any additions to the range are made above the bpttpm boundary by Inserting a Cells/Rows then it effectively becomes dynamic.

I have re-established this practise and it works. The red rows in attached image are included in their named ranges.

Thank you for all your help.

 

[Fluff]

Glad you sorted that & thanks for letting us know.