How to reference data arranged in rows in columns on same or another MSExcel Worksheet

I am trying to reference data arranged in rows and to show the data in these referenced cells in columns. Ideally I would reference the cells on the same MS Excel worksheet.

A reply given by Paul in this thread gave a partial solution. Using his suggestion, for a row of data on Sheet 2 this is referenced (i.e. rearranged into a column) on Sheet 1 when I have pasted '=OFFSET(Sheet2!$A$1,,ROW()-1)' in cell A1 on Sheet 1 and filled down for the number of rows in for the data on Sheet 2. My question is if it possible to do this for multiple rows of data on Sheet 2? For example, in the attached image of my MSExcel Table my goal is to end up with rows rearranged into columns. The rearranged cells that are to be referenced have formulas in them that need to be retained (i.e. a copy-paste with transpose does not work since the formulas are lost).

Any help or suggestions would be most appreciated!

Thanks,

Matt

image for offset.pdf