Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Transposing data (Excel 2003)

    I have Sheet1,C71:c127 and I want to automatically have the data from that column transposed into a row on Sheet2,d3:d123......without using cut & paste.....it has to fill in automatically as cells c71:c127 are populated......any suggestions ???

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transposing data (Excel 2003)

    I'm not totally clear on what you want. Transpose normally means that data in a row is transposed into a column, or data in a column is transposed into a row. However, the cell addresses that you gave are both columns, so it does not look like any transposing is required.

    Do you mean that whenever a value is entered into Sheet1!C71, that value automatically is shown in Sheet2!D3? If that is what you want, then couldn't you simply put the formula below into cell Sheet2!D3 and copy it to Sheet2!D4123.

    <code>
    =Sheet1!C71
    </code>
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transposing data (Excel 2003)

    Unless I'm missing something, you can simply put =Sheet1!C71 in cell D3 on Sheet 2 and copy the formula down to D123.

  4. #4
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Transposing data (Excel 2003)

    Post deleted by dmcnab

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Transposing data (Excel 2003)

    ..oops...my mistake (twice)...I want the 8 items of data from sheet 1, c2:c10 (these are in a column) transposed onto 8 cells in sheet 2, d10:j10 (these are a row in a different sheet)...thanks

  6. #6
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Transposing data (Excel 2003)

    ..oops...my mistake (twice)...I want the 8 items of data from sheet 1, c2:c10 (these are in a column) transposed onto 8 cells in sheet 2, d10:j10 (these are a row in a different sheet)...thanks

  7. #7
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transposing data (Excel 2003)

    In cell D10 on sheet2 =Sheet1!C2
    In cells E10:J10 =OFFSET(Sheet1!$C$2,COLUMN()-4,0,1,1)

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transposing data (Excel 2003)

    You do not need a different formula in D10, the second formula will work in the entire range.
    Legare Coleman

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transposing data (Excel 2003)

    Those two ranges are different sizes (the first is 9 cells and the second is 7 cells). However, mbarron's second formula should work if you put it in all cells in the Sheet2 range.
    Legare Coleman

  10. #10
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transposing data (Excel 2003)

    You are, of course, correct. I was thinking of a different method, then I remembered the OFFSET function.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •