Impossible 'Excel' question??

  Housten 17:07 13 Mar 2012

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 2012

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

  Zak 17:59 13 Mar 2012

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 2012

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 2012


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 2012


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.

Elsewhere on IDG sites

Best Black Friday Deals 2017

Black Friday Deals for Designers & Artists: Adobe, Apple, Corel Painter, Microsoft Surface, Wacom &e…

Best Black Friday Apple Deals 2017

Black Friday 2017 : date, sites participants & bonnes affaires