Excel 2010 - Sparklines Dynamic Range

[davidlim]

hi all,


am having crazy moment with dynamic range on sparklines.


my dynamic named range (offset) will always select the last 3-entries from a table. i named it "Last3"


using normal chart, the dynamic named range works. everytime i add new entries to the table, "Last3" will pick up the latest 3-entries (from bottom).


if i enter this Last3 named range into Sparkline's range, it works as well.


however, whenever i add new entries (e.g. 4 more lines), the sparkline will extend to the new entries. i.e the sparklines will have >4 data points.


upon checking the sparkline's data range entry, it has changed to absolute reference. manually, if i enter "Last3" name, the sparkline will update to latest 3 entries.


is there a way to make the Sparkline's data range "stick" to the named range (in this example, "Last3")?


thanks!

 

[Hui]

David


Can you post your named Formula so we can see what is going on?


Another way to do it is have the Sparkline fixed to 3 cells and have formulas in those cells that retrieve the last 3 data points from your data range

 

[davidlim]

hi hui,


here's the named range (it was used to pick last 30 entries):


=OFFSET(List!$B$4,COUNTA(List!$B:$B)-31,0,30,11)


it spans to 11 columns (different series), so the sparklines also spans 11 columns accordingly.


i actually put the sparklines above the column title of the table (sparklines are used to visualize the last 30 days of data; instead of hundreds of days)


re: your alternative. I have another table that uses this method. It works as it uses absolute reference.


but i was trying out dynamic named range as well