Impossible 'Excel' question??

  Housten 17:07 13 Mar 12

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.

  bremner 17:21 13 Mar 12

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

  Zak 17:59 13 Mar 12

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

  lotvic 18:11 13 Mar 12

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

  Zak 21:37 13 Mar 12


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.


  Housten 12:04 14 Mar 12


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.

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Here's what should be coming to Adobe Project Felix in 2017

Apple AirPods review: Apple's beautiful new Bluetooth headphones bring true intelligence to…