Indirect & Reference Cell Letter

[Apple1]

Using Excel 2010

I want to reference to Sheet 1 and different columns on the same row as I drag a formula down:
Example

Sheet 1! C39
Sheet 1! D39

1.
INDIRECT("Sheet 1!"&LEFT(ADDRESS(1, ROW(A3), 4, TRUE), (ROW(A3)>26)+1)&"$39/Sheet 1!$B39*100")


LEFT(ADDRESS(1, ROW(A3), 4, TRUE), (ROW(A3)>26)+1): gives letters from C onwards as I drag the formula down


For some reason, my Indirect formula doesn't work.

Thank you

 

[arthurbr]

Supposing your range extends from C39 to AA39 say and you start your references on row 1

to reference cells in the same row simply use =index(sheet1!$C$39:$AA$39,row()) and pull down as needed