# Thread: move data into row format (excel)

1. ## move data into row format (excel)

I am trying to move the data from worksheet

2. ## Re: move data into row format (excel)

I have attached a formula-based approach.

3. ## 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. ## Re: move data into row format (excel)

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

5. ## 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. ## 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. ## 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
•