Forum Discussion
Need help with Formula for incremental cell reference
This formula is in cell AR5 and filled down. The formula has to be entered with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
=INDEX($A$5:$A$25,SMALL(IF(NOT(ISBLANK($A$5:$A$25)),ROW($A$5:$A$25)-4),ROW(A1)))
This formula is in cell AS5 and filled across range AS5:AY7.
=INDEX($AI$5:$AI$25,COLUMN(A1)+(ROW(A1)-ROW($A$1))*7)
I assume that you don't work with Office 365 or Excel for the web. With older versions such as Excel 2013 you can apply the above formulas.
OK, so this is what you don't want! But, as a 365 developer, I would use nothing else.
There is one formula in cell AQ2 and that's it.
= LET(
facl, TAKE(WRAPROWS(facility,7), 1),
init, TAKE(WRAPROWS(initials,7),,1),
ftot, WRAPROWS(totals,7),
gtot, BYROW(ftot, LAMBDA(x,SUM(x))),
hdr, HSTACK("INIT", facl, "Total"),
body, HSTACK(init, ftot, gtot),
VSTACK(hdr, body)
)Had you required it, it wouldn't have taken much to calculate the facility totals from the daily totals within the formula.
Note: As of a couple of days ago, one line of code got a bit simpler on my insider beta Excel version
gtot, BYROW(ftot, SUM),
Thank you so much for taking the time to reply! I hope you have a wonderful week!
