Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    move data into row format (excel)

    I am trying to move the data from worksheet

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

    Re: move data into row format (excel)

    I have attached a formula-based approach.

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: move data into row format (excel)

    if I add data to the data work sheet do i use the handle to pull down to make it work for more data?

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

    Re: move data into row format (excel)

    Yes, the formulas can be filled down as far as needed.

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

    Re: move data into row format (excel)

    In what sense doesn't it work? You haven't put any formula at all in the workbook you attached.

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: move data into row format (excel)

    It seems to not wanna work with a huge load of data. do i need to manipulate the formula, maybe a quick explanation of how your formula works if you dont mind

    thanks

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

    Re: move data into row format (excel)

    For example, New Format!C4 contains the formula =IF(INDIRECT("Data!F"&10*ROW()-38)="","",INDIRECT("Data!F"&10*ROW()-38)). The essential part is INDIRECT("Data!F"&10*ROW()-38). The rest is just to prevent 0s from appearing when the source cell in the Data sheet is blank.

    C4 should contain the value from F2 on the Data sheet, C5 the value from F12, C6 that from F22, etc. As you see, the source row number increases in steps of 10. When we move one row down in New Format, we must move 10 rows down in Data. The ROW() function returns the row number of the cell that contains the formula. So for C4, ROW() = 4, for C5 it is 5, etc. We multiply this row number by 10, then subtract 38 to arrive at the corresponding row number for the source cell

    C4 -> 4 -> 10*4 = 40 -> 40-38 = 2
    C5 -> 5 -> 10*5 = 50 -> 50-38 = 12
    C6 -> 6 -> 10*6 = 60 -> 60-38 = 22
    etc.

    The expression "Data!F"&10*ROW()-38 results in the string (text) values "Data!F2", "Data!F12", "Data!F22" etc. The INDIRECT function retrieves the value of a cell whose address is supplied as a string.

Posting Permissions

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