Excel 2007 : from horizontal to vertical cell reference

Hi all,

I need to move data from a horizontal line in sheet 2 to a vertical line in sheet 1. That by itself I can do with the transpose funtion. The problem is that I have many horizontal lines on sheet 2 and I don' t want to have to type a new transpose formula for each line.

The trick I don't know is how, when copying this formula, I can get Excel to automatically change the cell references vertically. By default it seems to want to change the cell references horizontally. Unless you add the $A$1 to fix the cell to be referenced.

I think my question is somewhat similar to this thread - http://www.excelforum.com/excel-gene...ml#post2348213 - but that solution doesn't let me continue making vertical lists of data on sheet 1 from horizontal lists of data on sheet 2.

I'm including a simplified version of the spreadsheet I'm trying to use. Hope you guys can help.

Thanks!