sortinggoogle-apps-scriptgoogle-sheetsgoogle-sheets-formulagoogle-sheets-filter-view

How can I make a cell move depending on another cell?


I have a system where there are 2 columns of cells, and in these columns have names and data for the names.

I don't feel comfortable sharing my spreadsheet so here is a example.

Food \ Quantity
------ --------
Pasta  | 4

Lemons | 4

Chicken | 4

But now, code is sent to the quantity and the numbers change.

Pasta is now: 2
Lemons is still: 4
Chicken is now: 1

Now in the food column (Column E), I want it to move whenever I sort the Quantity column, but I don't want it to lose the correct number.

So Lemons would move as soon as 4 gets sorted to the top of the list. And Pasta would do the same but be 1 cell below. And chicken would be the last again, as if the food cells are basically following their quantity cells.

So now it would automatically update to this.

Food \ Quantity
------ --------
Lemons  | 4

Pasta | 2

Chicken | 1

And I want this to all happen without me manually touching it.

So what formula is needed for this? Im kind of new, so write to me like I'm a 4 year old.


Solution

  • select range like this:

    0

    then do:

    0

    then click on those 3 lines and sort it as you wish:

    enter image description here


    script:

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Sheet1");     //SHEET NAME
    var range = sheet.getRange("A2:Z");          //RANGE TO BE SORTED
    
    function onEdit(e)  {                        //NUMBER OF COLUMN(S) TO BE SORTED
      range.sort([{column: 4, ascending: false}, {column: 1, ascending: true}]);
    }