Results 1 to 6 of 6

Thread: Filters?

  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good evening

    I have been tossed a curved ball by one of my Directors which means making some reports for a board meeting at 12 romorrow (I am sweating already).

    Although I have never used filters I though this would be a good way to sort and view my columns in C5:C1073 which it does but it will then not allow me to sum the revenue, turnover etc. for the account I have selected because the cells are not consecutive.

    Can somebody tell me if there is a way (easy hopefully) of doing this.

    I can do it manualy but I wuld like to b finished sometime before 6am :-)
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you use the SUBTOTAL function (check the HELP) you can sum, average, count, etc the values that are displayed due to the filter and ignore the values that are "hidden".

    Steve

    PS you could also try a pivot table report to summarize particular data

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='stevehocking' post='777040' date='26-May-2009 19:12']Good evening

    I have been tossed a curved ball by one of my Directors which means making some reports for a board meeting at 12 romorrow (I am sweating already).

    Although I have never used filters I though this would be a good way to sort and view my columns in C5:C1073 which it does but it will then not allow me to sum the revenue, turnover etc. for the account I have selected because the cells are not consecutive.

    Can somebody tell me if there is a way (easy hopefully) of doing this.

    I can do it manualy but I wuld like to b finished sometime before 6am :-)[/quote]

    Hi Steve

    Thanks for the input, I could not get it working like it (I think) should have but the principal helped me to complete the task quicker than I could have otherwise have done. Hopefully the pressure will be off for this month but I will now start studying this feature in more depth for when they spring it on me in future months, stand by for more questions!!
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    [quote name='stevehocking' post='777123' date='27-May-2009 04:27']Hi Steve

    Thanks for the input, I could not get it working like it (I think) should have but the principal helped me to complete the task quicker than I could have otherwise have done. Hopefully the pressure will be off for this month but I will now start studying this feature in more depth for when they spring it on me in future months, stand by for more questions!![/quote]

    You are very welcome. But I do have to tell you that it seems to work like (I think) it should ()

    Perhaps when you get some time you could detail an example of what you wanted and we can try to give you an alternate solution that matches more of what you wanted/needed...

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='stevehocking' post='777040' date='26-May-2009 18:12'].........Although I have never used filters I though this would be a good way to sort and view my columns in C5:C1073 which it does but it will then not allow me to sum the revenue, turnover etc. for the account I have selected because the cells are not consecutive.............[/quote]

    You already known the Cell C5:C1073, unable to work with the "Auto Filter function" beacuse of : " Cells are not consecutive"

    In order to got it work, try………

    Assume your data range located at A51073, then in the empty column E, filled cells E2:E1073 all with "1"

    After that, the "Auto Filter function" will work properly

    Regards
    Bosco

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='bosco_yip' post='777286' date='28-May-2009 14:21']You already known the Cell C5:C1073, unable to work with the "Auto Filter function" beacuse of : " Cells are not consecutive"

    In order to got it work, try………

    Assume your data range located at A51073, then in the empty column E, filled cells E2:E1073 all with "1"

    After that, the "Auto Filter function" will work properly

    Regards
    Bosco[/quote]
    Thanks Bosco

    After speaking to Steve I realised that my problem was in that I was trying to manually sum by dragging across the cells on view and it would not do it. By using the autosum / sub total button the problem was eliminated.

    I have now, as Steve suggested moved onto Pivot tables and my bald patch where I am scratching my head is getting bigger

    Thanks for your interest and input
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •