Log In
Register
Home
December 4, 2021
Offline
Hello and Thanks in advance.
First I have a functional Dynamic formula (::: happy dance :::). It is already set-up to work off the Customer Name. Can the Invoice Date be added as a 2nd criteria?
Table Name: tbl_UPDATED
Criteria: $J$1 - this cell is a dropdown list of customer names
Criteria 2: $H$1 - This is The Invoice Date, displayed as tbl_UPDATED[Invoice Date] within the table.
=LET(f,FILTER(CHOOSECOLS(tbl_UPDATED,{2,1,5,3,4,6,10,11,12,13,15,16,17,19,20,21,22,23,24,25,26,27}),tbl_UPDATED[Customer Name]=$J$1,"No Values"),IF(f="","",f))
See Answer
Moderators
January 31, 2022
Offline
Thay would be:
=LET(
f, FILTER(
CHOOSECOLS(tbl_UPDATED,
{2,1,5,3,4,6,10,11,12,13,15,16,17,19,20,21,22,23,24,25,26,27}
),
(tbl_UPDATED[Customer Name]=$J$1)*(tbl_UPDATED[Invoice Date]=$H$1),
"No Values"
),
IF(f="","",f)
)
Place the 'include' statements between parenthesis and separate them by * for AND or + for OR.
By the way, there is no need for the LET and IF function within it. The filter is set-up to contain "No Values" if no rows are found that match the 'include' arguments. Hence, it will never return "". If you want FILTER to return a blank, just replace "No Values" with "".
Answers Post
December 4, 2021
Offline
@Riny van Eekelen,
That works great. But now my boss wants it to be sorted by a field. Let's say the field is tbl_UPDATED[Project Number] ? How do I incorporate that? He is talking about using that for the first one I created on only Invoice Date (see below), plus the second version you created, which contains 2 criteria. I am not sure how and where to indicate how I want to sort. Both will require keeping the criteria as is, then adding the sort aspect too. And FYI, tbl_UPDATED[Project Number] is in column 2.
Moderators
January 31, 2022
Offline
Hi Sherry,
I see you persist in using LET with a FILTER and an IF, although it serves no purpose. Best to focus on using FILTER alone.
Your CHOOSECOLS puts the 2nd column of the raw data, first in a filtered array of 12 columns. If you now want to sort by that column you can just wrap the entire formula in SORT. That would become like this:
=SORT(FILTER(.................))
That will default to an ascending sort order on the first column of the filtered (12-column) array. But you could easily sort in descending order on the 9th column (out of 12) by using the optional arguments of the SORT function. The sort_index would then be 9 and the sort_order -1.
Riny
1 Guest(s)
