Forum Discussion
Need help with Formula for incremental cell reference
This issue has 2 piece's:
I have been trying to figure out this formula for a while now. I am attempting to get the initials from Column A (that are every 7 rows) in order in Column AR. So AR5 should = AD. AR6 should = ADL. So on and so forth.
Then I need Colum AS5 to = AI5, AT5 = AI6, AU5 = AI7, AV5 = AI8, AW5=AI9, AX5=AI10, AY5=AI11
AS6=AI12, AT6=AI13.... ultimately looking something like this:
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.
In cell AR5:
=TOCOL(A5:A25,1)
In cell AS5:
=WRAPROWS(AI5:AI25,7)
With Office 365 or Excel for the web you can enter the above formulas in cells AR5 and AS5.
- I appreciate the response, but neither of these solutions worked for me. I was hoping whatever formula I used I would be able to drag down so that it would fill the cells as the data far surpasses the snip I took. Thanks for trying though.
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),- Peter,
Thank you so much for taking the time to reply! I hope you have a wonderful week!
