Create Dynamic Range From Pivot Table Data To Be Used In An Excel Chart?

TDC21

Board Regular
Joined
Mar 31, 2017
Messages
97
The end goal is to use a timeline slicer to update a Pareto chart. My current issues are,
1) A Pareto chart is not available as a pivot chart, the datasource has to be either a table or range.
2) The timeline slicer is not available for excel tables, only pivot tables.

My current efforts have been to load a datamodel into power pivot, create a pivot table, followed by many failed attempts at using a formula to create a dynamic cell reference to be used in the charts datasource so that it will update with changes made from using the timeline selection.

Im not exactly sure why this isnt working, each part works individually and when stepping through the calculation it appears to work right up until the last step. The detail states that "A function in this formula causes the result to change each time the spreadsheet is calculated. The final result will match the result in the cell, but interim steps may not"

=INDIRECT(ADDRESS(1,1)&":"&ADDRESS(ROW(Sheet2!REVENUE)+ROWS(Sheet2!REVENUE)-1,COLUMN(Sheet2!REVENUE)+COLUMNS(Sheet2!REVENUE)-1))

At this point Im not sure if Im even on the right path but if anyone has any advice on how I can acomplish this it will be greatly appreciated.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,890
I added "Sheet2!" so that a specific sheet is returned.
I added ", TRUE" so INDIRECT interprets its text as A1 format instead of R1C1 format

As a named range this returns the block of cells with the UL corner in A1
=INDIRECT("Sheet2!"&ADDRESS(1,1)&":"&ADDRESS(ROW(Book1!REVENUE)+ROWS(Book1!REVENUE)-1,COLUMN(Book1!REVENUE)+COLUMNS(Book1!REVENUE)-1),TRUE)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,486
Messages
5,636,610
Members
416,929
Latest member
Nitil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top