Nov 03 2021 01:11 PM
Nov 03 2021 02:29 PM
You may use SORTBY function (microsoft.com)
Nov 04 2021 01:43 AM
Nov 04 2021 03:34 AM
@Sergei Baklan is pretty good but I doubt even he can sort a .png file, an .xlsx would have helped.
Assuming you have named the columns or use structured referencing, something along the lines
= SORTBY(Table,
DateImported,1,
PlanNo,1)
would allow you to control the sort order of the output array.
Nov 04 2021 05:24 AM - edited Nov 04 2021 05:26 AM
In addition to @Peter Bartholomew explanation. In your case that could be like
=LET(
f, FILTER(<current expresssion>),
sortA, INDEX(f,0,3),
sortB, INDEX(f,0,4),
SORTBY(f, sortA, 1, sortB, 1) )
Nov 07 2021 02:42 PM
My apologies, I wasn't sure what is the easiest, but here is the file. Thank you for your kind help it is really appreciated.
Nov 08 2021 01:53 AM
Nov 08 2021 05:39 PM
Nov 08 2021 05:54 PM
Nov 08 2021 06:15 PM
Solution
Try this formula, (Don't forget to adjust the ranges before changing the name):
=SORTBY(FILTER('https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!$A$10:$V$49,ISNUMBER(SEARCH(LEFT('https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!$U$10:$U$49,LEN('https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!F3)),'https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!F3))),'https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!$C$10:$C$49,1,'Inv Nov'!$D$10:$D$49,1)
Cheers
Nov 08 2021 06:17 PM
Nov 08 2021 06:36 PM - edited Nov 08 2021 06:39 PM
If you want to spill it only by Columns and not the whole table rows, try this formula:
=FILTER(SORTBY(FILTER('Inv Nov'!$A$10:$V$49,ISNUMBER(SEARCH(LEFT('Inv Nov'!$U$10:$U$49,LEN('Inv Nov'!$F$3)),'Inv Nov'!$F$3))),'Inv Nov'!$C$10:$C$49,1,'Inv Nov'!$D$10:$D$49,1),('Inv Nov'!$D$10:$D$49='Inv Nov'!D16)*('Inv Nov'!$G$10:$G$49='Inv Nov'!G16))
Don't forget to adjust the range and change these:
'Inv Nov'!
To these:
'https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!
cheers
Nov 08 2021 06:43 PM
Nov 08 2021 08:24 PM
Nov 08 2021 08:28 PM - edited Nov 08 2021 08:33 PM
which cell does this formula reside=IFS(ISBLANK(B55),"",B55-C55)
=IFS(ISBLANK(B55),"",B55-C55=0,"1",ISNUMBER(B55),B55-C55,NOT(ISNUMBER(B55)),"Must be a number")
Nov 08 2021 08:34 PM
Nov 08 2021 06:15 PM
Solution
Try this formula, (Don't forget to adjust the ranges before changing the name):
=SORTBY(FILTER('https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!$A$10:$V$49,ISNUMBER(SEARCH(LEFT('https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!$U$10:$U$49,LEN('https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!F3)),'https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!F3))),'https://crockersnz.sharepoint.com/Body Corporate/Portfolio Spreadsheets/Portfolio 1/[Crockers - Invoices .xlsx]Rep Nov 21'!$C$10:$C$49,1,'Inv Nov'!$D$10:$D$49,1)
Cheers