SOLVED

# Need help with Formula for incremental cell reference

Copper Contributor

# 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:

6 Replies

# Re: Need help with Formula for incremental cell reference

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.

# Re: Need help with Formula for incremental cell reference

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.
best response confirmed by BlizzCo (Copper Contributor)
Solution

# Re: 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.

# Re: Need help with Formula for incremental cell reference

This worked perfectly! Thank you so very much!

# Re: Need help with Formula for incremental cell reference

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),``

# Re: Need help with Formula for incremental cell reference

Peter,

Thank you so much for taking the time to reply! I hope you have a wonderful week!
1 best response

Accepted Solutions
best response confirmed by BlizzCo (Copper Contributor)
Solution

# Re: 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.