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

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
> 24-08-15        242     Jacob's Ladder
> 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") to 
> create the header dynamically.
> 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