Oct 27 2023 03:36 AM
Hi,
I'm looking to add a SORTBY function to an existing FILTER formula.
The current formula I have to drag the below information from the main table is the following;
=FILTER(Table2[#All],Table2[[#All],[Tender Completed?]]="No",).
I want to add to this formula to make sure that in the 'Date Received' Column, the earliest dates are at the top.
Any help would be greatly appreciated!
Tender Number | Client | Project Title | Date Received | Return Date | Days Allowed to Price |
SGS2 | Example Company 2 | Example Job 2 | 21/10/2023 | 30/10/2023 | 9 |
SGS3 | Example Company 3 | Example Job 3 | 19/10/2023 | 24/10/2023 | 5 |
SGS4 | Example Company 4 | Example Job 3 | 19/10/2023 | 24/10/2023 | 5 |
SGS5 | Example Company 5 | Example Job 3 | 19/10/2023 | 24/10/2023 | 5 |
SGS6 | Example Company 6 | Example Job 3 | 19/10/2023 | 24/10/2023 | 5 |
SGS7 | Example Company 7 | Example Job 3 | 19/10/2023 | 24/10/2023 | 5 |
Oct 27 2023 04:19 AM
I'd use a separate row to return the headers:
=Table2[#Headers]
In the cell below the cell with that formula:
=SORT(FILTER(Table2,Table2[Tender Completed?]="No",), 4)
Oct 27 2023 04:20 AM
Or, in one formula:
=VSTACK(Table2[#Headers],SORT(FILTER(Table2,Table2[Tender Completed?]="No",), 4))
Oct 27 2023 06:24 AM
@Hans Vogelaar Thanks so much! for some reason its not liking me trying to turn the 'Outstanding Tenders' table into the same formatted table as the 'Tender Register' do you know a way around this?
Oct 27 2023 07:38 AM
Unfortunately, you cannot use a dynamic array formula in a table. Such a formula will return a variable number of rows, but a table cannot spontaneously resize itself to accommodate that.