Getting a Pivot Table to Update off of a Dynamic Range

[tpfennin]

I have construced a tool that updates automatically based off of a sql query and a dynamic named range of months. The month toggle updates the output page. Is there a way to tie that month toggle to a pivot table?

Everytime I update the month the pivot table, I have on another sheet, gets thrown off and i have to redo all the filters. I thought about possibly having the pivot table use the dynamic month list as a filter, I dont know how to get them linked.

Any thoughts?

 

[SkipVought]

hi,

Seems you have a DATE, not just a MONTH, as a criteria parameter for a query that you are running; yes? Or am I missing something?

So when you select your VALUE, it executes a query in some way. Please explain how.

Now you want to use that VALUE to filter a PivotTable?

Do you have any VBA code? VBA would be best addressed in forum707. Please post your questions and code there for better results. Otherwise, questions regarding spreadsheet features are best discussed in this forum.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tpfennin]

It is just a month field. I have a list of the 12 months as a named range which is then added as data validation on a single cell. That cell is referenced by all the formulas in the output sheet so as it changes the formulas pull in new data from the query.

The formulas in that sheet are referncing the query which updates once per month automatically.

I want to be able to control the pivot table(which is being displayed in tabular form) with the same single cell that controls the formuals and the query so that when it is toggled all the filters do not need to be adjested.

There really is no VBA code being used in the construction of the chart.

 

[SkipVought]

I guess I am confused, as simply using a month value in a database query, would return data for multiple years.

I do not understand how the selected month relates to your PT. Normally a query returns an indetermined number of rows. However if your PT uses a query, via MS Query, as the data source, the PT source range will automatically adjust to the number of rows returned by the query.

Please post some example that would clarify the issue.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

how does the pivot table relate to the query? fed directly from external data or based on a query table returned to a sheet?

Are you saying that there are multiple months worth of data feeding into the pivot table and you want it to show only the month that has been selected?


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[tpfennin]

The pivot table is created from and output of the query. So they are not directly linked in any way.

And yes there are multiple months worth of data being fed into the table, but only want the month that is selected in my month toggle cell to appear in the PT.

 

[SkipVought]

The pivot table is created from and output of the query. So they are not directly linked in any way.

Huh? Your QT is linked to your PT, via the PT SourceData.

want the month that is selected in my month toggle cell to appear in the PT.

Must be done via VBA code.

I suspect that you 1) update your query and then 2) go to your pivot table and refresh.

So, in your PROCESS, you would need to apply the selected month value to the month filter prior to refreshing. Via code, that would occur in the Worksheet_Activate event.

Turn on your macro recorder and record the PROCESS and post your recorded code in forum707, for help customizing and automating.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

If your pivot table is simply based on the query output with no extra calculations attached you could simply point the pivot table source to be the same as the query so that it uses the same parameter to fetch data - that way both your data sets are linked with no VBA needed

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Geoff, what you point out is true, which is why I questioned the OP's statement regarding the source of his PT.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

yep - need a bit more clarity on exactly how the pivot table is built!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question