Multiple Holidays in NETWORKDAYS function referencing another book

[rockja]

Hello,

I am trying to find a way to use multiple holidays in the NETWORKDAYS function by referencing another book and using a naming convention. This is a for a time sheet spreadsheet so it will be applied to 12 separate tabs.

Let's say I have a tab named Holidays and in this tab there is a table spanning the 12 months across the top and empty cells below each month column. In the column labeled Jan, which has two holidays listed 1/2/2012 & 1/16/2012, I selected the data cells and named it JanH in Name Manager. This works perfectly fine when using =NETWORKDAYS(1/1/2012,1/31/2012,JanH) .

If I have a separate master workbook listing the holidays in a data table and reference it in my time sheet workbook, excel doesn't like it too much... Does anyone have any thoughts?

Thanks!

 

[barry houdini]

Why do you need to split the holidays into months. Can't you just list all holidays and name that range holidays? It won't matter if you have blanks in the range

 

[rockja]

That will definitely help reduce the number of defined names, but it still doesn't solve the whole problem. Everything works well if I keep it all in the same workbook, however, if my list of holidays is in a separate workbook (not sheet or tab), Excel errors out the formula.

 

[rockja]

I feel like an idiot! It was as easy as opening the master workbook when defining the names in the time sheet book.