Dynamic Pivot Table Report Filters - Excel Tutorial

http://chandoo.org/wp/2011/04/27/update-report-filter-macro/

Learn how to dynamically update Pivot Table Report Filters using simple macro code. In this example you can see how this idea can help you create a dynamic chart from Pivot Tables. For details visit http://chandoo.org/wp/2011/04/27/update-report-filter-macro/

You can also download example files from there.

---

Closed Caption:

hi everyone welcome . orgy in this
tutorial we are going to know about
dynamic report filters and labels let me
show the demo once and then we can learn
how this technique is achieved
so here we have a paper chart that shows
the total sales by salesman and product
so we have four cents puzzles Joseph
Florence money and but and they also
three different products fast car a cure
to party so we are showing this somebody
for the middle region country i can
switch this region the mark or west and
dynamically this chart gets this chart
itself a chance you can see here from
the shampoos and the property P mr.
shape but the beauties we are calling it
through a drop-down looks like this so
that is much more natural and also it
looks quite honestly and insightful so
let me go ahead and demonstrate this to
you how this particular piece done what
we are doing is when you look at the
core / be totally stumped because and AD
chord behind this thing is just one line
that's right just want what I have here
is a couple of things going on process
to select this drop-down box which is
inserted into a form control it is
actually feeling to send III whatever
change the value greater that we've also
done cheers change actually d3 is
contestant number one advantage to have
made it have formatted in such a better
number will not be shown they say that
the details containing these values and
to work based on that what we are doing
is a little down here and pitching
responding region so this is the list of
regions of the pitch the second one is
lot
what you're getting yet and look at the
name here but this time i have given a
name stl region now we can study
convenience but i have created a new be
coming to be here looks a little more
immigrants in the cord the chapter XLV
dynamically generated text and the
chapter for you . two extra extra penny
helps sell Regenesis and now let me go
to be okay with you and this is the
paper TV which we creating the child
that the setting this also you can see
everybody wants to the people every
worksheet we got up your cheat and this
is the cell which is having report
printed now if you look at carefully
here you will be lost that this
particular cell also has a name
it says division we filter of creation
let's go to be the accord that so what
we are doing is whenever i change this
bob dole have ascended to a back-row an
update people will see that ok so the
update people black dragon which is the
model one object at all it does is it
takes top value is your region and puts
it in different regions that's on
whatever is the regional selector we
will not the worst interests asking
except take that value from here in this
particular collection so you don't
really have to update anything else
that's all you have to do is buy
cardelli lxm to take the value of the
value that one and click in this cell
here in the city so i could come here I
the middle and the exchange for million
i don't really have to use this card
normal course this is not for long it
takes
ok so i hope you understand that close
to take me here I have a little surprise
for you
what I have evacuated and I no
description for the chart also so if you
have downloaded the file you can click
on this little box here
how does this chalk you can see the
whole logic broken on essentially what
we are going to use whenever you change
this this list shows news value menu
change the list on a macro update be one
that makes the new vision and
particularly difficult people from ABC
can simply access permission that's how
this chatbox collapseexpand select a
powerful way to the dyno charts using
already available to chat your savings
and then combine it with simple one more
mac reports like this so i hope you
enjoyed this little rebellion if you
want to learn more visit changing the
world and if you would like to live
anymore work tables when I encourage you
to join make school program because we
have a lot of awesome content and their
payments including and one overall
structure by deborah our texture stock
on she lost a lot of your tables and she
has been microsoft and we can to me p
for the last seven years i believe so it
is a good program and if you can help
you love your babies and a lot of
chapter techniques a little bit of my
closest that horse backwards so go ahead
and join the strength to wish to learn
more
otherwise just go and play with this
file and limited more they have some
other examples here on the airport
preference which I'll share last longer
do so i'm just going to focus here on
this sheet
thank you very much for your pilot
action i wish you a great day