CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
Cyril Duchon-Doris
Cyril Duchon-Doris

Reputation: 14039

Use GSheet Interface to sort with data generated with ArrayFormula

I have a google spreadsheet (example) that has

I want my users to be able to sort easily the aggregated data, using the google interface (Menu data > Sort range). This is just an example so there's not a lof of data/columns, bu in the actual spreadsheet we used there are many many columns, the users may need to sort on any column.

I've seen multiple SO questions that deal with problems like sorting results computed from an arrayFormula, etc. but is there a solution out there, that is compatible with using filter from the GSheet interface ? So that someone with completely no knowledge of gsheet formulas cas still use the sheet ?

It's fine if there is a solution that involves creating a different sheet, or using different types of formula (query instead of arrayFormula, etc) that produce the same result.

Upvotes: 0

Views: 96

Answers (1)

Alessandro
Alessandro

Reputation: 2998

Considerations

Using the User Interface you can't sort the output of an ARRAYFORMULA. You will lose the corresponding cell reference in result of the sorting operation.

Using Query

Using the QUERY formula you can easily sort by any column using the order by operator.

In your case:

=QUERY('project-enhanced'!A1:C4, "select * order by C")

Now, since you don't want your users to write any formulas nor code you could build a simple User Interface to tweak your query formula:

=QUERY('project-enhanced'!A1:C4, "select * order by "&B1)

You can then make the B1 cell a user input field to specify the column letter he wants to sort the data by.

SORT BY:    C   
Project ID  Start date  Sum of estimations
2           11/14/2020  5
1           11/13/2020  30
3           11/16/2020  90

Reference

QUERY formula

Upvotes: 1

Related Questions