Forum Discussion
Is there a way to combine BYROW&BYCOL to make my formula SPILL across & down with one cell formula?
I have done it BYCOL and BYROW individually but am struggling to make it one... BYCOL: BYROW: Can anyone help make this one dynamic / spilled formula in the first cell? I...
=MAKEARRAY(ROWS(C38:C40),COLUMNS(E37:P37),LAMBDA(r,c,MAX(0,NETWORKDAYS(MAX(EOMONTH(INDEX(E37:P37,c),-1)+1,INDEX(C38:C40,r)),MIN(INDEX(D38:D40,r),EOMONTH(INDEX(E37:P37,c),0))))))
Following your suggestion MAKEARRAY and LAMBDA could be an alternative.
=MAKEARRAY(ROWS(C38:C40),COLUMNS(E37:P37),LAMBDA(r,c,MAX(0,NETWORKDAYS(MAX(EOMONTH(INDEX(E37:P37,c),-1)+1,INDEX(C38:C40,r)),MIN(INDEX(D38:D40,r),EOMONTH(INDEX(E37:P37,c),0))))))
Following your suggestion MAKEARRAY and LAMBDA could be an alternative.
Thanks, Oliver - both are perfect. Really appreciate your help.
The BH_Table is just a list of Bank Holidays. So I've amended to include that and all is perfect and as expected.
The BH_Table is just a list of Bank Holidays. So I've amended to include that and all is perfect and as expected.
