2015 Archives by date, by thread · List index

# Re: [libreoffice-users] How to know if a value passes a filter in a formula?

```Regina,

Thanks for the help, works like a charm.
And yes, I've now read the full help of SUBTOTAL() and I see all kind of uses :-)

Thanks again,

Rob.

On 9 okt. 2015, at 18:12, Regina Henschel wrote:

```
```Hi Rob,

if you filter using an Autofilter, then you can use function
SUBTOTAL(4;<Range>)
instead of MAX. See help on SUBTOTAL.

Kind regards
Regina

Rob Jasper schrieb:
```
```Hi,

I have a list of music pieces with dates they are, or will be handed out.
The format looks like:

26-10-15     11A     Stille Nacht
26-10-15     12A     Bethlehem
26-10-15     28      Ere zij God
26-10-15     265     See amid the winter's snow
M    275     Dicht bij het hart van God
24-08-15     363     Zie de Zon
5-01-15      369     Heer mijn hart zoekt u te vinden
24-03-14     396     Geef aan de wereld vrede
M    453     Dank zij U Heer
24-08-15     456     Sanctus
M    480     The Creation
24-08-15     493     The Exodus Song
26-10-15     498     For unto us a child is born
29-09-14     499     Dochter van Sion
29-09-14     500     Zingt een nieuw lied (naar Psalm 98)
5-01-15      502     Wees stil voor het aangezicht van God
5-01-15      503     'k heb gehoord van een stad
5-01-15      504     Van Oranje
14-12-15     509     Nooit meer nacht
9-02-15      510     Zing voor de Heer met hart en stem
7-09-15      511     Somebody bigger than you and I
14-12-15     512     Wilhelmus Cantate

The header line should contain "Per <hjighest date> in the binder".
I have now the formula =CONCATENATE("Per ",TEXT(MAX(B1:B9999),"dd-mm-yyyy"), "  in the binder")
It now shows: Per 14-12-2015 in the binder

What I want is that if I filter 14 dec 2015 in the list, that then the header shows 26-10-2015.
So the formula needs to be 'filter aware'.

How can I accomplish this?

Rob.

```
```

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
```
```

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
```