Paste Special/Transpose, but retain cell reference?

[dotrain4u]

Hello,

In cell B3 I have the name of a state and a city underneath (done with Alt+Enter). Cell C3 has another state/city, cell D3 another, and so on down row 3.

I need to paste those names elsewhere vertically. So, I am using Paste Special/Transpose. This gives me the text from B3, C3, etc., only. What I REALLY need to paste vertically is the =sheetname!B3 reference so that if the town name changes in the original horizontal row, the vertical one changes too. I have tried pasting formula transposed, but that doesn't work. I searched the boards, but after reading about 20 possible similar entries, I didn't find a match.

One other issue is that when I paste, the Alt+Enter has turned into a symbol box that is visible in the vertical column. Help!

 

[bryonwoods30]

in the formula that you are copying and pasting, use $ in the cell references so that when you copy, paste and transpose them the references won't change.

 

[dotrain4u]

Hi Byron...

Thanks for taking a stab at this. The tricky part is that what I am copying and pasting is not a formula, it's just state/city name. When I paste it though, then what I want to paste would be the cell reference (=sheetname!B3) (then C3, then D3, etc.). Maybe you are right about absolute value, but I had tried that and it didn't work.