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
> > 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


-- 
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