Re: [libreoffice-users] Calc- How to count selected lines

The table was really messed up- So here it is in text:

ID      Pgs     Beg     Map     Aktie   Titel
1                                       Volk'ren, hoort!
2                                       Heer, wees mijn Gids
3       1                               De waldhoorn
4       1                               O Heer, die daar (Val.Gedenckklank)
5A      2                               Ei oechajem
5B      1                               Soldatenlied
6       2                               Die zwölf Räuber
7                                       0 bone Jesu
8       1                               Nu jubelt en wees blij
9       2                               Het Gebed (zie 162)
10      1                               Wat de toekomst brengen moge
11A     1               19-10   i       Stille Nacht
12A     1               19-10   i       Bethlehem
12B     2                               Ik kniel aan Uw kribbe neer
13      1               5-01    i       Piet Hein (de Zilvervloot)
14      2                               Sanctus (uit de Deutsche Messe V)
15      1       P                       Ambrosianischer Lobgesang
The second looks like:

Seq     Datum   Nr      Titel
1       24-08-15        242     Jacob's Ladder
2       M       275     Dicht bij het hart van God
3       M       279     Geduchte God hoor mijn gebeden
4       M       305     Abba Vader
5       24-08-15        363     Zie de Zon
6       05-01-15        369     Heer mijn hart zoekt u te vinden
7       24-03-14        396     Geef aan de wereld vrede
7       04-04-16        433S    Aan de Voorthuizense dreven
8       M       453     Dank zij U Heer


On 30 mrt. 2016, at 16:30, Rob Jasper wrote:

> Hi all,
>
> maybe I have a blind spot, but I don't see the solution..
>
> I have a long list (in my case of music numbers, with titles, dates, and admin stuff.
>
> it looks like:
>
> Nr    Sub     ID      Pgs     Beg     Map     Aktie   Titel
> 1     
> 1     
>
>
>
> Volk'ren, hoort!
> 2     
> 2     
>
>
>
> Heer, wees mijn Gids
> 3     
> 3     1       
>
>
> De waldhoorn
> 4     
> 4     1       
>
>
> O Heer, die daar (Val.Gedenckklank)
> 5     A       5A      2       
>
>
> Ei oechajem
> 5     B       5B      1       
>
>
> Soldatenlied
> 6     
> 6     2       
>
>
> Die zwölf Räuber
> 7     
> 7     
>
>
>
> 0 bone Jesu
> 8     
> 8     1       
>
>
> Nu jubelt en wees blij
> 9     
> 9     2       
>
>
> Het Gebed (zie 162)
> 10    
> 10    1       
>
>
> Wat de toekomst brengen moge
> 11    A       11A     1       
> 19-10 i       Stille Nacht
> 12    A       12A     1       
> 19-10 i       Bethlehem
> 12    B       12B     2       
>
>
> Ik kniel aan Uw kribbe neer
> 13    
> 13    1       
> 5-01  i       Piet Hein (de Zilvervloot)
> 14    
> 14    2       
>
>
> Sanctus (uit de Deutsche Messe V)
> 15    
> 15    1       P       
>
> Ambrosianischer Lobgesang
>
> Now I have an other tab where I select only things with a value in the "Map" column
>
> 1     24-08-15        242     Jacob's Ladder
> 2     M       275     Dicht bij het hart van God
> 3     M       279     Geduchte God hoor mijn gebeden
> 4     M       305     Abba Vader
> 5     24-08-15        363     Zie de Zon
> 6     05-01-15        369     Heer mijn hart zoekt u te vinden
> 7     24-03-14        396     Geef aan de wereld vrede
> 7     04-04-16        433S    Aan de Voorthuizense dreven
> 8     M       453     Dank zij U Heer
>
> I use the fomula =IF(AND(OR(Lijst.$F2="M",ISNUMBER(Lijst.$F2)),NOT(Lijst.$G2="i")),Lijst.$C2,"") 
> in the third column. 
> Subsequently I use a filter to just filter anything with a value in the 3rd column.
>
> Question 1:
> Is there a more clever way of doing this, so that I don't need the manual action to adapt the 
> filter if something changes in the main list?
>
> Question 2:
> I want a sequence number in the first column- I started out with using SUBTOTAL(2,<range>) for 
> this. This didn't work since not all values are numeric (e.g. 433S). 
> COUNT(D$4:D..) has the same problem as can be seen in above example (twice nr 7)
>
> Any suggestions?
> thanks,
> 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