Indirect formula to increment cell the cell AND the column?

Question

I have a worksheet name in cell C11 which I am referencing in a formula with a =INDIRECT formula.

I have also added the =ROW formula so that it increments the row number when I drag it down my worksheet.

My formula looks like this: =INDIRECT("'"&$C$11&"'!C"&ROW(C12))

How can I modify it so it also increments the column when I drag it right?

I've tried to use the =column method, but I assume I am doing something wrong as I just get a reference error, can anybody assist?

Answer 1

Column() returns the Column number, so you may use the R1C1 variant of INDIRECT.

=INDIRECT("'"&$C$11&"'!R"&ROW(C12)&"C"&COLUMN(C12),FALSE)

Greetings

Axel

Answer 2

A bit long, but working. Assume A2 has the =Column(A1) formula for the whole row.

Isert into A3 and copy to the while row:

=IF(A2>26,CHAR(64+ROUNDDOWN((A2+1)/26,0))&CHAR(64+A2-26*ROUNDDOWN((A2+1)/26,0)),CHAR(64+A2-26*ROUNDDOWN((A2-1)/26,0)))

This works only for the one and two letter columns. You can expand if you need after ZZ column.