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

[TDC21]

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.

 

[pbornemeier]

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)