Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Sort Rows A - Z messing up formulars

Options
Heath Sanders
edited 12/09/19 in Archived 2017 Posts

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 ?

 

 

Filter.PNG

Comments

  • Jason Anderson
    Options

    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)

    chrome_2017-10-09_08-13-48.png

    chrome_2017-10-09_08-18-47.png

  • Heath Sanders
    Options

    Thanks Jason 

This discussion has been closed.