Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: Columns to rows

  1. #11
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Chris and rdeters,

    Chris, I was really impressed by your great effort in solving RDETERS question, which you did brilliantly. Yet you yourself asked for more suggestions to an “easier or tidier way“…I think I’ve got one, which uses the INDIRECT formula in a simple way.

    1) In the other worksheet (sheet2) choose any column, say “D” and enter in

    D1 -> "Instrument Tags!T3"
    D2 -> "Instrument Tags!U3"
    D3 -> "Instrument Tags!V3"
    D4 -> "Instrument Tags!W3"
    D5 -> "Instrument Tags!X3"
    D6 -> "Instrument Tags!Y3"
    D7 -> "Instrument Tags!Z3"

    2) Now select D1:D7 and drag all the way down to D3500 in one stroke, the row numbers will grow by 1 every 7 rows

    3) Put in cell A1: =INDIRECT(D1) and copy it down 3500 rows.

    I think that this is a simple approach, which utilizes the Excel property to raise numbers in fixed increments and repeat sequences when dragging them from cell to cell.

    I’ll appreciate your opinion about it.

    Regards,

    Eli






    [ This Message was edited by: eliW on 2002-05-09 11:20 ]

  2. #12
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Eli,

    spot on.... much less complicated formulae too

    I just noticed where I was going wrong offline in your example.... your initial sheet names aren't actually links, they are just manually typed in, hence the autofill incrementation exploitation

    works well




  3. #13
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes Chris, that's what I meant.
    I wasn't clear enough in my first reply - Look at the eddited one, I hope it's more clear now.
    Regards,
    Eli

  4. #14
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'll be honest Eli...

    I never knew you could autofill like that, with ranges...

    I do singles all the time just never made the connection with ranges

    thanks ! I owe ya

    Chris

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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