Incrementing dynamic ranges beyond Column Z

Hello everyone!

I commonly run into a brick wall when developing dynamic ranges that extend beyond the 26th column, especially in conjunction with INDIRECT.

The voices in my head insist the solution involves the use of IF, MOD and QUOTIENT and the darned number 64 but I just can't quite finish it.
There has to be some way to account for moving from Z1 to AA1.

I get as far as =CHAR(IF(64+ROW(A1)>90,64+ROW(A1)-26,64+ROW(A1))) and that will repeat the alphabet at row 27+ but I'm not sure where to go from there.

indirect column increment beyond Z.xlsx

Here's a more robust way to get the column letter that goes beyond column Z

=SUBSTITUTE(ADDRESS(1,ROW(A1),4),1,"")


However, you really don't NEED to use the column letter here anyway.
You can use the column Number directly in an Index function.

Try this for your formula in B2
=INDEX(Sheet2!$1:$1,ROW(A1))

Thank you both for your suggestions!

This is exactly what I needed (I was clearly overthinking the solution). I've bookmarked this for future reference.