Forum Discussion
Can Net.workdays.intl spill(#)?
Geir, I'd recommend to avoid INDIRECT() as much as possible. Two reasons
1) It affects performance which could be not important for relatively small data set
2) It's not suitable from maintenance point of view. If, for example, one day you decide to insert another column between F and G, or move the range to the left/right, you shall manually change "G" within INDIRECT() on another column.
Variant could be, if you have the spill in F8,
=NETWORKDAYS.INTL(+F8#,+G8:INDEX(G8:G100,ROWS(F8#)))as
It works if you first convert the range to array, e.g. by applying some dummy arithmetic operation. This and some other functions don't do that automatically. For example
Thank you SergeiBaklan I found another solution, based on your input.
I Used INDIRECT to build the refference for the last row in the dynamic table.
NETT.WORKDAYS.INTL(+F8:INDIRERECT("F"&ROWS(F8#)-1+8);+G8:INDIRECT("G"&ROWS(F8#)-1+8);1;Datalister!$C$6:$C$17))
ROWS# calculate number of rows in the dynamic array
+8 because the first row is 8
-1 to adjust for the first row.
If its two rows with data, the result will be 2-1+8, the refference will be F9
Best Regards
- Geir
Geir, I'd recommend to avoid INDIRECT() as much as possible. Two reasons
1) It affects performance which could be not important for relatively small data set
2) It's not suitable from maintenance point of view. If, for example, one day you decide to insert another column between F and G, or move the range to the left/right, you shall manually change "G" within INDIRECT() on another column.
Variant could be, if you have the spill in F8,
=NETWORKDAYS.INTL(+F8#,+G8:INDEX(G8:G100,ROWS(F8#)))as
