subsetting by dollar amount
 

Hi,

I have a pivot table based on an access query. It gives sales by month and
then a total by customer. The totals by customer vary from $100 to $100,000.
I want to be able to somehow filter out amount based on a dollar amount that
I type in. For instance, I only want to see those clients with totals
5000 or 50000. Is there a way to create an input box where I can type in a
dollar amount and have the report filter all amounts below the amount that I
type in?

Thanks,

--
Chuck W

subsetting by dollar amount
 

Chuck,

One easy way is to use another column of formulas in your database table
(insert a column so that it is included in your pivot table source).

Use a formula like:

=IF(B2=$H$2,"Include","")

and then copy that down. Set up your PT to show only values of that field
where the entry is "Include", and then when you want to change the level,
enter the new cutoff level in cell H2, and refresh your pivot table.

HTH,
Bernie
MS Excel MVP


"ChuckW" wrote in message
...
Hi,

I have a pivot table based on an access query. It gives sales by month
and
then a total by customer. The totals by customer vary from $100 to
$100,000.
I want to be able to somehow filter out amount based on a dollar amount
that
I type in. For instance, I only want to see those clients with totals
5000 or 50000. Is there a way to create an input box where I can type
in a
dollar amount and have the report filter all amounts below the amount that
I
type in?

Thanks,

--
Chuck W

subsetting by dollar amount
 

Bernie,

Thanks for your help. I am not fully understanding the steps though. Do I
create a field in my Access DB table and populate it with something? I am a
novice Excel user so any additional explaining would be helpful.

Thanks,

--
Chuck W


"Bernie Deitrick" wrote:

Chuck,

One easy way is to use another column of formulas in your database table
(insert a column so that it is included in your pivot table source).

Use a formula like:

=IF(B2=$H$2,"Include","")

and then copy that down. Set up your PT to show only values of that field
where the entry is "Include", and then when you want to change the level,
enter the new cutoff level in cell H2, and refresh your pivot table.

HTH,
Bernie
MS Excel MVP


"ChuckW" wrote in message
...
Hi,

I have a pivot table based on an access query. It gives sales by month
and
then a total by customer. The totals by customer vary from $100 to
$100,000.
I want to be able to somehow filter out amount based on a dollar amount
that
I type in. For instance, I only want to see those clients with totals
5000 or 50000. Is there a way to create an input box where I can type
in a
dollar amount and have the report filter all amounts below the amount that
I
type in?

Thanks,

--
Chuck W

subsetting by dollar amount
 

Chuck,

After you do the data extract from Access, add in the column of formulas,
then do your pivot table. You probably could do your extract to pull a
limited set of data, but then you wouldn't be able to look at the different
levels without re-doing the extract.

HTH
Bernie
MS Excel MVP

"ChuckW" wrote in message
...
Bernie,

Thanks for your help. I am not fully understanding the steps though. Do
I
create a field in my Access DB table and populate it with something? I am
a
novice Excel user so any additional explaining would be helpful.

Thanks,

--
Chuck W


"Bernie Deitrick" wrote:

Chuck,

One easy way is to use another column of formulas in your database table
(insert a column so that it is included in your pivot table source).

Use a formula like:

=IF(B2=$H$2,"Include","")

and then copy that down. Set up your PT to show only values of that
field
where the entry is "Include", and then when you want to change the level,
enter the new cutoff level in cell H2, and refresh your pivot table.

HTH,
Bernie
MS Excel MVP


"ChuckW" wrote in message
...
Hi,

I have a pivot table based on an access query. It gives sales by month
and
then a total by customer. The totals by customer vary from $100 to
$100,000.
I want to be able to somehow filter out amount based on a dollar amount
that
I type in. For instance, I only want to see those clients with totals

5000 or 50000. Is there a way to create an input box where I can
type
in a
dollar amount and have the report filter all amounts below the amount
that
I
type in?

Thanks,

--
Chuck W