Can Formulae work only on Filtered Data?

Discussion in 'Computer Software & Operating Systems' started by KeithO, Dec 29, 2011.

  1. KeithO

    KeithO
    Active Member

    Joined:
    May 28, 2003
    Messages:
    1,434
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    63
    Location:
    Erding, Bavaria
    Ratings:
    +214
    Hi gurus!

    I am working with Excel 2010. I have a spreadsheet with around 40k rows, and maybe 50 columns. The first 8 columns contain my 'raw' data (extracted from another source and cut/paste into Excel). The remainder of the columns are derived data dependant upon the values (or combinations of values) in the first 8 columns.

    All this data is in a single worksheet. Additional worksheets contain control data (which influences the derived columns in the data worksheet via vlookup, etc), summary data (extracted from the data worksheet via countif, etc) and graphs based on the summary data.

    What I would like to do is to filter the data in the main worksheet so that I can select on certain values in the 8 columns of raw data, and then have the summary/graphs reflect only the 5000 rows (or whatever) of filtered data.

    The filtering of course if easy, but as the 40k rows are really still there in the data worksheet (just hidden by the filter) I can't get the summary/graphs to work only on the filtered data.

    Is this possible? It seems like it should be. I'm fairly new to Excel 2010, so is there something I'm missing here?

    Thanks in advance! :smashin:
     
  2. KeithO

    KeithO
    Active Member

    Joined:
    May 28, 2003
    Messages:
    1,434
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    63
    Location:
    Erding, Bavaria
    Ratings:
    +214
    No one? I was really hoping this would be simple to do, but I can't find anything in the help nor on the web :thumbsdow
     
  3. grahamlthompson

    grahamlthompson
    Distinguished Member

    Joined:
    Aug 3, 2006
    Messages:
    24,170
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    166
    Location:
    Redditch
    Ratings:
    +4,107
    Very easy to do with VBA (Visual Basic For Applications).

    Quick work around select and copy the cells after filtering. Paste Special Values only into a seperate sheet with this data linked to your graphs.
     
  4. Mr Incredible

    Mr Incredible
    Distinguished Member

    Joined:
    Dec 2, 2003
    Messages:
    23,701
    Products Owned:
    2
    Products Wanted:
    0
    Trophy Points:
    166
    Location:
    The Fylde Coast
    Ratings:
    +13,239
    Wouldn't a pivot table / pivot graph be able to do both the selecting and graphing in one go, as it were?
     
  5. grahamlthompson

    grahamlthompson
    Distinguished Member

    Joined:
    Aug 3, 2006
    Messages:
    24,170
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    166
    Location:
    Redditch
    Ratings:
    +4,107
    Smart idea :smashin:
     
  6. Mr Incredible

    Mr Incredible
    Distinguished Member

    Joined:
    Dec 2, 2003
    Messages:
    23,701
    Products Owned:
    2
    Products Wanted:
    0
    Trophy Points:
    166
    Location:
    The Fylde Coast
    Ratings:
    +13,239
    Not just a pretty face! ;)
     
  7. KeithO

    KeithO
    Active Member

    Joined:
    May 28, 2003
    Messages:
    1,434
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    63
    Location:
    Erding, Bavaria
    Ratings:
    +214
    I did try playing around with pivot table this afternoon, so maybe I was on the right track - just need to get my head around it now!

    Thanks guys, will give it a go :smashin:
     
  8. grahamlthompson

    grahamlthompson
    Distinguished Member

    Joined:
    Aug 3, 2006
    Messages:
    24,170
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    166
    Location:
    Redditch
    Ratings:
    +4,107
    Judging by your Avatar, hardly :rotfl:
     

Share This Page

Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice