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.
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.