Dynamic graph with dynamic pivot

[jayalaxmi]

Hi Team,

Please help me with a solution to automate the graph with the pivot data without using table. My graph needs to be refreshed when the pivot data changes.

VBA macro solution is also fine. Please find attached the sample file for reference.

Thank you in advance.

Regards
Jaya

 

[GraH - Guido]

Hi, since you are using a pivot chart, the chart is changing along with the pivot refresh.
I believe you are after a dynamic range to define the pivot source data. When consulting the name manager I see some attempts to do so with offset.
So I went with that.
Look for vPivData in the name manager. It seems to work.
Not sure though it is an answer to your query.

 

[jayalaxmi]

Hi, since you are using a pivot chart, the chart is changing along with the pivot refresh.
I believe you are after a dynamic range to define the pivot source data. When consulting the name manager I see some attempts to do so with offset.
So I went with that.
Look for vPivData in the name manager. It seems to work.
Not sure though it is an answer to your query.

Hi

Thanks for your reply.

It seems to be working fine.Can you please let me know what you had done to get it.

One more thing, I can see is the select data range for the graph seems to be greyed out.

Regards
Jaya

 

[GraH - Guido]

Hi the data range for the chart is greyed out, since it is a pivot chart.
The dynamic range is done in the name manager via an offset formula.

example tutorials

Dynamic Named Range in Excel

A dynamic named range in Excel expands automatically when you add a value to the range.

www.excel-easy.com

or https://www.ozgrid.com/Excel/DynamicRanges.htm

 

[jayalaxmi]

Thank you GraH