Simplify Networkdays Function, contains several IF

Question

I regularly receive spreadsheets where I need to calculate the Networkdays between 2 columns of dates.

A holiday date range also needs to be included in the function. There are 10 possible holiday ranges (named Prov1, Prov2, etc.) that I have stored in a separate spreadsheet.

The formula below selects the appropriate holiday date range per the province and is working. But I will need to expand it to cover 10 provinces so it will become a bit cumbersome.

   =IF(M18="Prov1",NETWORKDAYS(M21,N21,Holidays.xls!Prov1),_
    IF(M18="Prov2",NETWORKDAYS(M21,N21,Holidays.xls!Prov2)))

Is there a way to simplify this formula? Or a better approach?

Answer 1

If you do not mind using a volatile formula, then the INDIRECT function should suffice.

=NETWORKDAYS(M21,N21, INDIRECT("Holidays.xls!" & M18))