You can do this using Indirect combined with Column() (for dragging across) and Row() (for dragging down).
This in H8 dragged across will increment the row numbers:
Formula:
=INDIRECT("'Report (USD)'!$C$"&COLUMN(J1))
This in H8 dragged down will increment the column numbers:
Formula:
=INDIRECT("'Report (USD)'!$"&SUBSTITUTE(ADDRESS(1,ROW(A3),4),"1","")&"$10")
So, since you want to do both, use this combination in H8, which you can then drag across and down:
Formula:
=INDIRECT("'Report (USD)'!$"&SUBSTITUTE(ADDRESS(1,ROW(A3),4),"1","")&"$"&COLUMN(J1))
However, in this case, since you want to drag columns down rows and rows across columns, is there any reason why you can't swap your rows for columns? That way you could just drag down to increase the rows and drag across to increase the columns, without any need for using Indirect with Row/Column at all.
Edit: here are the formulae in German, from this site: https://en.excel-translator.de/translator/
Formula:
=INDIREKT("'Report (USD)'!$C$"&SPALTE(J1))
=INDIREKT("'Report (USD)'!$"&WECHSELN(ADRESSE(1;ZEILE(A3);4);"1";"")&"$10")
=INDIREKT("'Report (USD)'!$"&WECHSELN(ADRESSE(1;ZEILE(A3);4);"1";"")&"$"&SPALTE(J1))
Bookmarks