PDA

View Full Version : [SOLVED] Indirect & Networkdays Functions - Tab Reference



BrI
06-30-2017, 01:12 PM
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
06-30-2017, 02:33 PM
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
06-30-2017, 03:01 PM
Works fine for me.

BrI
07-01-2017, 08:06 AM
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
07-01-2017, 08:10 AM
Please post a workbook, rather than screenshots.

BrI
07-01-2017, 08:20 AM
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
07-01-2017, 09:02 AM
Create a range name BC for Holidays2017!B2:B16 and use =NETWORKDAYS(TabA!B3,TabA!C3,INDIRECT(TabA!$A3))

mdmackillop
07-01-2017, 09:12 AM
Alternatively, set up your holiday dates as a table and use
=NETWORKDAYS(TabA!B3,TabA!C3,INDIRECT("Table1[@" & TabA!$A3 & "]"))

BrI
07-01-2017, 09:15 AM
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
07-01-2017, 09:22 AM
For clarification, the alternative solution does not require you to create named ranges.

BrI
07-01-2017, 09:32 AM
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.