Contact Forum Editor

Send an email to our Forum Editor:

PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

Tech Helproom

It's free to register, to post a question or to start / join a discussion

View Forum Rules | Contact Forum Editor

 

Impossible 'Excel' question??

Housten

Likes # 0

Posted March 13, 2012 at 5:07PM

Good Afternoon,

I know a small amount about excel but I think what I want to do is impossible, BUT if someone can prove me wrong I would be very, very happy.

What has happened I created a spreadsheet some time ago and the data I have entered, and will continue to enter goes down the spreadsheet, 'vertically' shall I say. I have created a second sheet and realised that some of the data I wanted was actually on the first sheet, so I thought it would be easy to copy. WRONG!! The ata on the second sheet is 'horizontal'! So what I am trying to do is to copy information from, say, c4 to c300 into the second sheet in c23, d23, e23, f23...... So what I need is to be able to make 'Name$C4' in c23 [ which I can just copy in as it is the first ], and then copy that into d23 which will have 'Name$C5' - by some instruction to change/add to the formula, and then in e23 have it equal 'Name$C6'. As I said I think that this can not be done but.......Excel has some very interesting instructions within it and I am just wondering if this change could be one of them. If anyone knows how it can/could/can not be done I will be very grateful. To be honest a negative answer will be useful, it will mean spending some time re-doing the sheet, but I will at least know it is impossible.

Many thanks to any and all who reply.

Like this post
bremner

Likes # 0

Posted March 13, 2012 at 5:21PM

Use copy and then paste into second sheet using paste special chosing Transpose from the menu.

Like this post
Zak

Likes # 0

Posted March 13, 2012 at 5:59PM

This will provide you with the formulas, but you must allow the array to include for further entries e.g C4:C1000; once set you cannot change without re-doing the process of setting up the column array.

Excel TRANSPOSE Function

Like this post
lotvic

Likes # 0

Posted March 13, 2012 at 6:11PM

Tip: Use Sheet 3 (a blank sheet) to Paste Special Transpose into then you won't mess up the original sheet.

Paste Special Transpose a couple of links

Rotate data by converting columns to rows (or vice versa)

paste copied cells in a transposed direction

Like this post
Zak

Likes # 0

Posted March 13, 2012 at 9:37PM

Housten

Add to my entry above:

You will also need to ensure your selection of the row on sheet 2 also allows for further entries e.g. C23, C24, ..........C1023; otherwise the formula will not pickup the values that you will be entering in the future.

Zak

Like this post
Housten

Likes # 0

Posted March 14, 2012 at 12:04PM

Gentlemen,

bremner, Zak and lotvic,

Many, many thanks for all your speedy replies. I will now have to settle down and work out how to use your replies and incorporate them into my spreadsheet. Just a matter of time, I assure you. We pensioners take that little longer to get our heads round new things like this, but get my head round it I will. I can see me having further uses of it as well, so I am very, very grateful to all of you!

many,m many thanks again, because I can not emphasise how useful this will be to me.

Like this post

Reply to this topic

This thread has been locked.



Check out PC Advisor's other tech forums

Send to a friend

Email this article to a friend or colleague:

PLEASE NOTE: Your name is used only to let the recipient know who sent the story. Both your name and the recipient's name and address will not be used for any other purpose.