I need to re-order rows of formulas. In a corner?

[John.McLaughlin]

I have 6 rows of formulas for each of 50 different items, making 300 rows total, all derived from the results of 50 items currently on another sheet.

I need to move (re-order) the bottom 2 rows (now rows 5&6), up to the beginning position making them rows 1&2, and move the current rows down to positions 3-6.

I need to do this 50 times, ugh. Is there an easy button? Or must I replace = with #, then re-order things by hand?

Thanks in advance!

 

[GaryWMn]

John,

What if you added a sort column adjacent to your formulas? Add 3,4,5,6,1,2 in sequence top to bottom. Since your formulas are pointing to cells on another sheet, when you sort, the formulas would move still pointing to the correct target cells. You could use the ROW() formula to create your first sequence of numbers and then copy down the sequence to fill the remaining of the 300 rows.

The first sequence of rows might look like this:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Comment</td><td style="font-weight: bold;;">SortColumn</td><td style="font-weight: bold;;">RefFormula</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">This results in the number 3  --></td><td style="text-align: right;;">=ROW(B3)</td><td style="text-align: right;;">=Sheet2!A2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">                                       4  --></td><td style="text-align: right;;">=ROW(B4)</td><td style="text-align: right;;">=Sheet2!A3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">                                       5  --></td><td style="text-align: right;;">=ROW(B5)</td><td style="text-align: right;;">=Sheet2!A4</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">                                       6  --></td><td style="text-align: right;;">=ROW(B6)</td><td style="text-align: right;;">=Sheet2!A5</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">                                       1  --></td><td style="text-align: right;;">=ROW(B1)</td><td style="text-align: right;;">=Sheet2!A6</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">                                       2  --></td><td style="text-align: right;;">=ROW(B2)</td><td style="text-align: right;;">=Sheet2!A7</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />
After the 300 rows are filled, you can copy/paste special values to convert your numbers to values and then sort based on the SortColumn.

Others may have a slick VBA solution, but this should get you what you want to do.

Let us know if this works for you.

Gary

 

[John.McLaughlin]

Thank you Gary, if I follow your suggestion, and understand Excel... the columns/rows are in pretty deep between other data.

Basically, I am creating another sheet based on the data and only then did I realize I put the last one in the wrong order, or rather, not the best order.

I may just leave it be, and change the call sequence on the next sheet. It's just not as tidy as I like.

I appreciate your response, and didn't want to leave it unanswered. Have a great day.

 

[GaryWMn]

John,

You should be able to add the helper column adjacent to your data set, perform the sort and then delete the helper column, leaving your data intact, in order and the way you want it.

Let us know if you need clarification on any of the steps.

Gary

 

[John.McLaughlin]

You are awesome Gary, I did it the hard way already

Changed = to #, copied all the formulas to a new sheet. Copied the 2 out of place rows into the correct order, then put them all back.

Thanks again, I have no formal training in this and learn as I go. MrExcel.com is a terrific resource!