Quantcast

Can Formulae work only on Filtered Data?

KeithO

Novice Member
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:
 

KeithO

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

grahamlthompson

Distinguished Member
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.
 

Mr Incredible

Distinguished Member
Wouldn't a pivot table / pivot graph be able to do both the selecting and graphing in one go, as it were?
 

grahamlthompson

Distinguished Member

KeithO

Novice Member
Wouldn't a pivot table / pivot graph be able to do both the selecting and graphing in one go, as it were?
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:
 
Top Bottom