Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Dragging (filling) transposed values (Excel 2000 SP3)

    Is there a way to drag (ie. autofill) a cell to pick up the values from the opposite axis (a bit like Paste Special, Transpose).

    Better explained by an example:-

    I have a 2 worksheet model.

    In sheet 1 I have a row of values on, say row 10.

    In sheet 2 I want to pick up those values, but place them in a column.

    If I put a formula in the first cell which reads =Sheet 1!A$10, I get the correct value.

    I would then like to be able to drag that corner of that first cell down the column, such that my column would autofill with the sheet one values from cells A10, B10, C10, D10 etc.

    Obviously you need a command which tells you that you are applying a transposition, or the cells picked up will be A11, A12, A13 etc.

    Is there a way?

    Thanks

    Neil

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dragging (filling) transposed values (Excel 2000 SP3)

    Let's say your "first cell" is D5. Enter the following formula in this cell.
    <code>
    =INDEX(Sheet1!$A$10:$M$10,ROW()-ROW($D$5)+1)
    </code>
    Change the range A10:M10 as needed. You can fill down the formula.

Posting Permissions

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