Excel Fill Formula

[Sheizageek]

I have a rather large workbook with 5 worksheets. I am trying to copy data from sheet1 column B8 to sheet2 column A1 respectively (='sheet1'!B$8). I would like to copy the formula down so that it changes the column letter and keeps the row number. . . i.e. C8, D8, E8 etc. the first formula reads ='sheet1'!B$8 and I manually entered the second and third to read ='sheet1!C$8 and ='sheet1'!D$8. When I try to drag the formula down, it only repeats those three entries and does not change the column letter. I have opened Formulas and set Calculation Options to Automatic as well as gone into options to make sure my fill handles are turned on. Any other suggestions?

Thanks

 

[Z51]

You could use this in cell A1 and fill down:

=OFFSET(Sheet1!B$8,0,ROW(A1)-1)

 

[Sheizageek]

=OFFSET(Sheet1!B$8,0,ROW(A1)-1)

Thank you