3

I have this spreadsheet (link to google drive --- notice that the online app is not showing the filtering, you need to download it...):

starting spreadshhet

It works very well and lets me see the total when I filter either with criterion 1 or 2 (or both). Now, I would like to have in cells B8 and C8 the "selected" value in the filter:

wished result selecting b

and if I change the filter:

wished result selecting a

...and similarly on the B8. I do not mind if, when selecting just Crit1, the B8 cell is blank or with a random value of the list. In the real application, the C8 cell value will be used to index inside another table.

The idea is "put in B8 what is displayed two rows above, minding the filtering" --- is that possible in calc? (Or excel, I do not mind switching).

4
  • You can upload the file elsewhere and edit the question to include the link.
    – Gantendo
    Mar 18, 2022 at 11:01
  • 1
    @Gantendo thanks, done (I was not sure if it's accepted practice here)
    – Rmano
    Mar 18, 2022 at 11:11
  • 1
    Sorry, my answer was not to the point. If you wish to get the last non-blank value in column C rows 2-6, you may use in C8 the formula =LOOKUP(2,1/(1-ISBLANK(C2:C6)),C2:C6). Does that work for you?
    – harrymc
    Mar 18, 2022 at 12:30
  • @harrymc I tried it, but it says always b, no matter the filtering...
    – Rmano
    Mar 18, 2022 at 13:40

2 Answers 2

3

You could set it up so that enter a value in the cell filters the table and not the other way round.

enter image description here

Here I have defined two named cells in G2 and H2 and called them p_crit1 and p_crit2. The p is for "parameter". You can of course call these whatever you want, or not name them at all, but I think this is more easily understood.

The full data table is in cells A1:E6. I have formatted this as a Table so that any references to it in other formulas will grow if more data are added to the table. This table is called full_data. You might consider having this on another hidden sheet, or hide the columns containing the full data.

In cell G5 I have this formula to get the headers:

=full_data[#Headers]

In cell G6 I have this formula to get the filtered data:

=LET(qcrit1,FILTER(full_data,full_data[Crit1]=p_crit1,full_data),FILTER(qcrit1,INDEX(qcrit1,,3)=p_crit2,qcrit1))

Let's look at this more closely:

=LET(
    qcrit1,
    FILTER(
        full_data,
        full_data[Crit1]=p_crit1,
        full_data
            ),
    FILTER(
        qcrit1,
        INDEX(qcrit1,,3)=p_crit2,
        qcrit1
        )
    )

Let the name qcrit1 (query by criteria 1) be the result of the FILTER of full_data where the full_data[Crit1] column is equal to the value in p_crit1. If the attempted filter returns an empty set (i.e. there is no match), then just return the entirety of full_data. This means if a user enters an invalid value in p_crit1 (cell G2) or enters nothing in that cell, we will just get all the data.

Now, filter the data returned by the first filter (called qcrit1) where the third column (accessed via INDEX) is equal to the value in p_crit2. If the attempted filter returns an empty set (i.e. there is no match), then just return the entirety of qcrit1. This means if a user enters an invalid value in p_crit2 (cell H2) or enters nothing in that cell, we will just get whatever was returned by qcrit (the first filter).

The net result is that we can change the values in cells G2 and H2 and see the filtered dataset change.

The subtotal formulas are above the filtered dataset so that it has room to change shape depending on the amount of data in full_data and what query criteria are selected. They are applying the sub-total to the 4th and 5th columns of the dynamic array reference $G$6#, which will automatically pick up all the data returned by the filter. You can of course use MATCH in place of hard-coding the column indexes 4 and 5 if you want to.

The benefit of this approach is that you can now easily refer to the p_crit1 and p_crit2 in as many places in your workbook as you want. You can optionally add data validation to cells G2 and H2 to limit the values the user can select. These can then be all possible Crit1 and Crit2 values, or a subset of each.

enter image description here

1
  • Really nice! A pity that OO Calc has no FILTER... ;-)
    – Rmano
    Mar 18, 2022 at 15:36
1

You can achieve it with a helper column and TEXTJOIN() available in Excel 365:

  • formula in helper column: =SUBTOTAL(2,D4)
  • formula for totals row: =TEXTJOIN(", ",TRUE,UNIQUE(FILTER(B2:B7,$E$2:$E$7=1)))
  • formula for sum: =SUM(FILTER(D2:D7,$E$2:$E$7=1))

of course you can even hide your helper column

enter image description here

enter image description here

1
  • Nice, thanks. A pity UNIQUE and FILTER are not in LO calc...
    – Rmano
    Mar 18, 2022 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.