Excel 2007 : Drag Fill cells with Formulas

This is a two part question: I have two worksheets within 1 file. I am taking 1 column of part numbers and copying them to put them onto another worksheet but they need to go into a row rather than a column. I am doing a paste special then checking transpose which allows me to achieve changing the row to column but when I go back to paste with all those cells highlighted and try to apply the paste link, it does not work. If I try to manually put in about the first 10 cells in the string so that the formula shows a pattern and then I try to do a drag fill, it doesn't work. It only fills the same first 10 over and over again. Is it possible to do a drag fill when it comes to formulas?

So say the formula is ='48 Section'!A2 ='48 Section'!A3 ='48 Section'!A4 ='48 Section'!A5 ='48 Section'!A6 ='48 Section'!A7 etc. When I either do a drag fill or highlight all the cells on the pasted cells in the other worksheet and do a print link, it doesn't work. What am I doing wrong.

You might be able to do it with another formula instead.

e.g

something like:

=INDEX('Sheet1'!$B:$B,COLUMNS($A$1:A$1))

When you enter this formula in the new sheet, it looks at column B of the Sheet1 and extracts the item at the top of the range (i.e. B1).. as you copy it across the cells horizontally, it extracts the next vertically sequential cell value in Sheet1...

Does that work for you?

Of course, adjust sheet name and range to suit.