Is there a way to disregard the header and footer of an indexed data?
I need to dynamically get only the dates only that will be used on another dynamic formula.
My code is not working as $L$2:$L$4 contains Holiday and END text. I am planning to use index so that i can only get the dates.
=NETWORKDAYS.INTL(A2,B2,1,$F$2:$F$4)
Holiday column (Column F) is designed that way so that i can insert a row before END row adjusting all connected formula such as my NetworkDays (screenshot).
EDIT: Adjusted the formula
>Solution :
You could try using the following formula, if you are using MS365
Using DROP( ) twice.
• Formula used in cell C2
=NETWORKDAYS.INTL(A2,B2,1,DROP(DROP($F$1:$F$3,1),-1))
You can also use the formula in this way, by sorting the range.
Using DROP( ) & SORT( )
• Formula used in cell C2
=NETWORKDAYS.INTL(A2,B2,1,DROP(SORT($F$1:$F$3,,-1),2))
Or, Using TOCOL( )
=NETWORKDAYS.INTL(A2,B2,1,TOCOL(--$F$1:$F$3,2))
You can use OFFSET( ) as well, but note that, since its a volatile function, it does not just recalculate anytime something on the sheet changes, it will recalculate anytime anything on any open excel workbook changes! Posted as an alternative approach.
• Formula used in cell C2
=NETWORKDAYS.INTL(A2,B2,1,OFFSET(F:F,1,,COUNTA(F:F)-2))
Another alternative approach, which avoids the use of volatile functions:
• Formula used in cell C2
=NETWORKDAYS.INTL(A2,B2,1,$F$2:INDEX(F:F,MATCH(7^89,F:F)))
Test cases:
Note: Based on one’s Excel Version, may need to hit the CTRL+SHIFT+ENTER while exiting the edit mode.