Sort Rows A - Z messing up formulars

Heath Sanders

Hi Team,

I use the top row in my sheet to calculate data in the sheet, because I cant freeze the top row when I do a "Sort Rows A - Z" it moves the formula even though I use ([$Row$1]:[$Row$50].......

I was looking at Cell linking the rows with the date I need in another sheet so the top row in the new sheet would never need to "Sort Rows A - Z" because that was its only purpose.  The problem is when I move the rows in the old sheet "Sort Rows A -Z"  the cell links in the new sheet don't like it which defeats the purpose of having "cell link" in the first place in this situation.

Is there a formula that I can use that no matter where the rows are moved around in the old sheet the new sheet wont be affected by it ?

 

 

Jason Anderson

I know you can select only specific rows and then right click and sort. That may help you avoid sorting the top row into your data rows.

You would just select the second row, scroll down to the end of your data rows, hold shift and select your last row of data. Right-click and select sort rows. 

 

Another thing you can try is having your first row as a parent row and all the rows under it as children. you can then refer to them in formulas as CHILDREN(), no matter how many you have. That would avoid setting a specific range in your formula. (Second screenshot)

Heath Sanders

Thanks Jason