Solved: transposing in Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

etaf

Wayne
Thread Starter
Moderator
Joined
Oct 2, 2003
Messages
65,486
I have values in a spreadsheet
In reference
A1 B1 C1 D1 etc

if i want to copy and paste a formual to transpose

so for example
in A10
I have = A1
in A11
I have = B1
in A12
I have = C1

is there a way to do that with copying

I have copied the values and paste special value - transpose
but that only copies values and does not pick up any changes
 

Attachments

Joined
Dec 4, 2007
Messages
2,895
Highlight the values you would like to copy (A1 thru D1) -> Right-Click & Click Copy -> Click inside cell "A10" -> Right-Click & Click Paste Special -> Check 'Transpose' -> Click OK.

The values should copy down as needed.
 
Joined
Dec 4, 2007
Messages
2,895
I have copied the values and paste special value - transpose
but that only copies values and does not pick up any changes
So, if you make changes in A1 thru D1m you would like that to automatically change in A10 thru A13? Will the values ever go past D1?
 

etaf

Wayne
Thread Starter
Moderator
Joined
Oct 2, 2003
Messages
65,486
I'm not after the values - I want the reference to the cells

so A10 becomes " =A1 "
and A11 becomes " =B1"
 
Joined
Dec 4, 2007
Messages
2,895
See if this is what you are after....

1) Paste the following in A10.....=TRANSPOSE($A$1:$D$1)
2) Press Enter
3) Select A10
4) Highlight from A10 down to A13 -> Press F2 -> Press CTRL+SHIFT+ENTER

This should do what you are asking. I have re-attached a copy of the file you originally attached. Take a look at cells A15 thru A18.
 

Attachments

Joined
Sep 4, 2003
Messages
4,916
Take a look at the INDIRECT function

EDIT: Nevermind I just looked at your sample Workbook and see that this is not what you are looking for.

Regards,
Rollin
 
Joined
May 4, 2002
Messages
743
This may seem a bit hokey :p- and there is no doubt a slicker way to do it - but at least this down and dirty will do the do until that comes along. It requires that you add an alpha column for reference. You could hide it if you choose.

- Castleheart
 

Attachments

etaf

Wayne
Thread Starter
Moderator
Joined
Oct 2, 2003
Messages
65,486
thanks rollin_again, I have been playing with the indirect and unable to make it work, so thanks for the update.

The array version using =transpose has worked ok on my original - thanks gamecockfan

Thanks CastleHeart, the transpose array worksOK - I have a large number of diferent ranges to do,
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top