We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

hide cookie message
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


 

Impossible 'Excel' question??


Housten

Likes # 0

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

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

Like this post
Zak

Likes # 0

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

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

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

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.



IDG UK Sites

Swatch to release its own line of smartwatches to rival iWatch

IDG UK Sites

From the iPhone 6 to the iWatch and a new Apple TV we look at the products Apple is set to launch...

IDG UK Sites

Miranda July's Somebody app offers a very unusual take on messaging

IDG UK Sites

The 7 most ridiculous iPhone 6 rumours: what Apple WON'T reveal on 9 September