View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
Dawg House Inc Dawg House Inc is offline
external usenet poster
 
Posts: 22
Default Using Pivot Table to Display Filtered Data Only

Thanks again Dave. I don't think that's going to work for this situation as I
basically want to have them select the "filter values" from a list. In a
perfect situation, they'd select the values from a list box and the values
would get filtered as the values were selected in the list box. I'm all about
writing code...just not sure if its possible to pass in the values for the
filter.

ColA | ColB | ColC
------------------------------
Ctry1 | Val1 | Code1
Ctry2 | Val1 | Code2
Ctry3 | Val2 | Code2
....
Ctry99| Val15 | Code1
=================
Basically, I'd like the Col A values to be available in a single
multi-select list that would impact the view of the filtered area of the
spreadsheet.

Make sense?

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Dave Peterson" wrote:

If I have a list of, say, states that I want to filter by, I could put that list
in column A of another sheet.

Then I could use a formula like:
=isnumber(match(b2,sheet2!a:a,0))

If B2 contains the state name for that row.

But you could use any formula you wanted:
=or(a2="Smith",b2="mi",c21000)
So I could retrieve the rows associated with Smith or Michigan or greater than
1000.

It's really more of an ad hoc formula that does what it needs to do for that
single purpose.


Dawg House Inc wrote:

Thanks for the suggestion Dave. I checked out the "easy filter" option, but
that's not an option for me in this case. I am trying to take the spreadsheet
and have a way to present the data in an easy to use (the users will be
selecting the values to filter) way that doesn't require the user to do
anything but select the data elements they want (or don't want) to see -
multi-selecting would be great (if an option).

I'm not a pivot table person per se, so I'm not sure on the capabilities of
them nor their extensive features. I simply use them for summary information.
As for your "formula in a helper column", would you care to share an example
of this so I can see if it meets the needs of the users ?

Thanks again.
Cheers,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

"Dave Peterson" wrote:

If I want to filter on multiple values in a field (or even multiple values in
multiple fields), I sometimes create a formula in a helper column and filter by
that.

I can make that formula as complex or as simple as I want.

I could even use that helper column as a page field in the pivottable.

Ron de Bruin has an alternative:
http://www.rondebruin.nl/easyfilter.htm

Dawg House Inc wrote:

OK, I'm trying to do the following:

I have a spreadsheet with the sample columns: Region | Country | Cust_Seg |
Classification | twelve more columns

Since Excel won't let me filter on more than two items using auto filter,
I'm trying to utilize a pivot table (so I was told) to display the data I
want. In short, I want the user to be able to select 0, 1, or many (even all)
Regions and or Countries and or Cust_Segs and or Classifications (all the
combinations) and display the other twelve fields as well with their related
data.

If I could use the auto-filter to allow them to pick/filter more than two
items using the "custom" feature, then this would not be a problem. Also, I
don't need to see any totals, counts, products, etc for the final results.
I'm not even sure a pivot table is the answer (I don't think it is), but I've
been directed twice by coworkers to consider "the pivot table". While they
are great for summary information, totals and the likes, I don't think
they're going to give me the answer I want. Are they??

Pivotal Decision Awaiting on Pivot Tables,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

--

Dave Peterson


--

Dave Peterson