Using Dynamic Ranges with Sparklines

[shepardb]

I thought I finally figured this out using the information here: http://www.mrexcel.com/forum/excel-questions/692415-sparkline-dynamic-range.html, but I can't get the named range to stick as the sparkline range.

I'm trying to create sparklines for data in a pivot table that can have a variable height and width. I've successfully created named ranges using OFFSET, COUNT, and COUNTA that update dynamically when the pivot table range changes.

My data ranges are:
Name: PCBSAYPivotTableRange
Formula: =OFFSET($C$5,0,0,COUNTA($B$5:$B$1000),COUNTA($C$4:$AA$4))

Name: PCBSAYSparklineRangeForumla: =OFFSET($A$5,0,0,COUNTA($B$5:$B$1000),1)

When I set the sparkline group data range and location to PCBSAYPivotTableRange and PCBSAYSparklineRange respectively, it works! However, when I edit the sparkline data range again, PCBSAYPivotTableRange and PCBSAYSparklineRange are replaced by the actual data range (e.g. $C$5:$D$118 and $A$5:$A$118). While the named ranges properly update when the pivot table changes, the names I've created to be dynamic change to static ranges in the sparkline data range and location range. How can I get the sparklines to maintain my named range and actually be dynamic?

 

[FlameRetired]

Do any of the ranges in the Dynamic Named Ranges include blanks or any empty strings ("")? And do they contain numbers or text ... mixed?

 

[shepardb]

Do any of the ranges in the Dynamic Named Ranges include blanks or any empty strings ("")? And do they contain numbers or text ... mixed?

The data within the ranges is continuous, but the references extend beyond the data. If that makes sense. Essentially the ranges extend beyond the data and I'm relying on COUNTA to determine where the data stops. There aren't empty cells within the data. The data referenced are strings, none are strictly numbers.

The named ranges work correctly and dynamically set the correct range initially. Apparently, I just can't use a named range in a sparkline and have it update dynamically. It will change the named range to a static range of cells. I finally found a forum entry that clarifies that: https://social.technet.microsoft.co...spond-to-extended-ranges-in-excel?forum=excel. They'll work for charts, just not for sparklines.

I think I'm out of luck. I really wanted to be able to quickly visualize the tabular data across a row in a cell adjacent to the data, but not being able to make the range dynamic won't really work for me.

 

[FlameRetired]

Didn't know that. Sorry.