Indirect & Networkdays Functions - Tab Reference

[BrI]

Trying use Networkdays along with the Indirect function to pick up a range of holidays on the "HolidayTab" of a workbook. There are 10 different ranges of holidays in this tab and the range selected will depend on the value in column A in TabA.

Note, I am working in "TabB"

I have this function working if I reference the holiday ranges in a separate workbook (Holidays.xls) as below.

Working:

=NETWORKDAYS(TabA!E17,TabA!F17,INDIRECT("Holidays.xls!" &TabA!A17))

But I want to have the holiday ranges in a separate tab in the same workbook as TabA and cannot get this to work. Have tried variations on formula below but not working.


Not Working

=NETWORKDAYS(TabA!E17,TabA!F17,INDIRECT("HolidayTab!" &TabA!A17))

Can someone help with the syntax?

[BrI]

Maybe this is a setting issue or even a bug ..... I have working in a similar spreadsheet using a tab reference and even working with no reference to the tab holding holidays at all. But cant get working is the subject spreadsheet.

Would like to know the issue, not very familiar with Indirect.

[Bob Phillips]

Works fine for me.

[BrI]

Thanks for looking at this and encouraging working for you. I still can't get to work. Created a new file to test and getting #Ref! > Invalid Cell Reference error. I included some screen shots below - hoping may help ID the problem.

[mdmackillop]

Please post a workbook, rather than screenshots.

[BrI]

Thanks,

Attached is a sample workbook. Dates etc are in TabA, formula is in TabB, and Holidays are in Holidays2017 tab.

Getting # Ref error.

[mdmackillop]

Create a range name BC for Holidays2017!B2:B16 and use =NETWORKDAYS(TabA!B3,TabA!C3,INDIRECT(TabA!$A3))

[mdmackillop]

Alternatively, set up your holiday dates as a table and use
=NETWORKDAYS(TabA!B3,TabA!C3,INDIRECT("Table1[@" & TabA!$A3 & "]"))

[BrI]

Fantastic! that works - much appreciated, spent hours on this.

Also, as soon as I named the range the existing formula (below) started working.

=NETWORKDAYS(TabA!B3,TabA!C3,INDIRECT("Holidays2017!" &TabA!$A3))

[mdmackillop]

For clarification, the alternative solution does not require you to create named ranges.

[BrI]

Thanks, that may explain why some of my other "holiday" workbooks were functioning as I had been occasionally using tables but never named a range for these.