Dynamic charts using formulas only (i.e. without named ranges and VBA)

0
votes

I've recently asked a question about dynamic ranges, and all the answers were based on named ranges. If I look further on SO, I find other answers to similar questions which also use named ranges.

The problem with named ranges is their creation/maintenance can't be automated (e.g. if I want 20 charts I need to manually create named ranges for those 20 charts).

I was hoping to create dynamic charts based on formulas only (which would be defined in the chart SERIES and edited manually very quickly (much more quickly than having to create named ranges), thus charts could be added/removed without having to maintain any named ranges.

Is there a way to create dynamic charts using formulas only (i.e. without named ranges and VBA scripts)?

1
exceldynamicchartsexcel-formulaexcel-2010

1 Answers

1
votes

Short Answer: No

Longer answer: If you have the name function pointing to parameters on your spreadsheet, that would be as easy to maintain as having the ranges in text on your spreadsheet.

E.G. Name Data1 can be defined as =Offset(Indirect(B1),0,0,B2,0), where the sheet looks like this:

Data Location        Sheet1!A7
Data Points          5

Also, the definition of your title defines how many points are seen, so even if you have the data defined as 5 point as in the example, if your titles only have 3 points, then only 3 points will be seen