Excel 2007 : Cut\transpose vs. copy\transpose

Does anyone know if there is there a way to cut and transpose in order to retain the exact formulas you have in cells?

I have approximately 100 cells going across row 1 that reference column titles in another tab. I want those same 100 cells in a vertical list instead of a horizontal list so that if a title changes on Sheet 1 it will carry over to the other tab. When I use copy\transpose, the cells referenced in the formula change. I want them to stay the same.

In other words, I am looking for a way to cut\transpose or a way to copy\transpose and retain the original formulas exactly without having to go into every cell and add update the referenced cell on Sheet 1 or without having to go into every cell and add "$" before the column/row letters and numbers.

Here is the sample code:
Sheet 2
cell A1: =Sheet1!A1
cell B1: =Sheet1!B1
cell C1: =Sheet1!C1
cell D1: =Sheet1!D1
etc.

I do not have the option to cut\transpose and when I copy\transpose into cell A3 on Sheet 2, I get the following formulas:
cell A3: =Sheet1!A3
cell A4: =Sheet1!A4
cell A5: =Sheet1!A5
cell A6: =Sheet1!A6
etc.

I want to have the following result:
cell A3: =Sheet1!A1
cell A4: =Sheet1!B1
cell A5: =Sheet1!C1
cell A6: =Sheet1!D1
etc.

Any assistance you can offer would be appreciated! Thanks much!

I'm not sure about cut and transpose, but you may want to try this formula as an alternative.
In Sheet2 A3, paste: =OFFSET(Sheet1!A1,3-ROW(),ROW()-3)

Simply drag this down the column and you should have what I think you were looking for.

Hope this helps!


SC

The offset formula worked great! Thanks so much for the assistance. This is going to be a big time saver in the future!