Autofill does not seem to work when dragging right

[karlos]

Excel two Sheets

Sheet1 Cell A1 has in it =sheet2!$C1 (sheet 2 C1 holds text)

I select the cell for auto fill ie along the column every cells gets the
same formula.

Drag it down the column and C on is fixed but the numeric part increments
as it should do - why does it not work when I pull it right across the row?

Any insight would be most appreciated

 

[Wigi]

When you drag this formula to the right, you stay in the same row so the row
number will not increment.

 

[karlos]

Ok but if you put 1 in on cell and 2 in another cell it increments along the
row,
similarly the column value a, b .c etc will also increment so why does the
cell row number not increment if the column is fixed as absolute, is there
any way round this? I need to copy about 1000 rows of 25 different refences
the only thing that cange is a letter d for the next row e for the f for the
next etc and 1 to 25 along the rows

so

on row 1 starting A1 =sheet2!C1 through to =sheet2!C25

then row 2 starting A2 = sheeet2!C26 through to = sheet2!C51

and then for the next 1000 lines or so

autofill would be so useful if it worked