Chart data range based on value (range) in cell

[chielvandijk]

Hello,

I have some formulas generating a data range for a chart (Charts("Age")).
However, I don't seem to get this dynamic data range into the chart's data range.
I've read that it's impossible through 'regular Excel', but I haven't succeeded with VBA so far either.
I've tried using a named range (Range("[FILENAME.xlsm]Data!Index")) for my dynamic data range as well.

Is there someone who can assist me with this matter ?

 

[Smitty]

Welcome to the Board!

Have you tried a Dynamic Named Range?

 

[chielvandijk]

Welcome to the Board!

Have you tried a Dynamic Named Range?

Thanks.

A Dynamic Named Range won't work because the last 4 rows of data shouldn't appear in the chart.
And through the dropdown menu the number of rows change:

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"][TABLE="width: 139"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Overview!A34:A48[/TD]
[/TR]
[TR]
[TD]Overview!E34:F48[/TD]
[/TR]
[TR]
[TD]Overview!H34:I48[/TD]
[/TR]
[TR]
[TD]Overview!K34:R48[/TD]
[/TR]
[TR]
[TD]Overview!T34:AE48[/TD]
[/TR]
[TR]
[TD]Overview!AG34:AK48[/TD]
[/TR]
[TR]
[TD]Overview!AM34:AY48[/TD]
[/TR]
[TR]
[TD]Overview!BA34:BD48[/TD]
[/TR]
[TR]
[TD]Overview!BF34:BN48[/TD]
[/TR]
[TR]
[TD]Overview!BP34:CB48[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="class: xl67"]
[/TD]
[/TR]
</tbody>[/TABLE]


The 48 changes to 41, 45, 38, 46, 37, 42 or 46, depending on the dropdown menu.
These formulas generare automatically, and I need to find a way to insert the actual formula into the Chart data range, and not just the cell in which they are located.

[TABLE="width: 72"]
<colgroup><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[chielvandijk]

Anyone who might've a solution to the problem above ?

 

[chielvandijk]

Bump

 

[chielvandijk]

Some clarification:

I have a sheet filled with graphs and one dropdown menu (7 menu options).
Another sheet is filled with values per category (one graph per category in the other sheet) and also contains one dropdown menu (with the same link as the one in the other sheet).
Each of the menu options from the dropdown causes the values to change, which it also does in the graphs.
But because every menu options causes the amount of values along the x-axis to change, I need my chart's data range to change as well.
In a third sheet I have formulas (generated through the dropdown link number) which calculate the range.
I need a way (dynamic range, or <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> for instance) to get this range as my graph's data range, instead of the cell in which the range can be found becoming the range. And the range needs to change along with the formula's output change when the dropdown menu is changed.

 

[Smitty]

A Dynamic Named Range won't work because the last 4 rows of data shouldn't appear in the chart.

Sure it can, just use -4 on the COUNTIF part of the OFFSET formula.

 

[chielvandijk]

Sure it can, just use -4 on the COUNTIF part of the OFFSET formula.

I've tried it like this:

Defined names
Index - =OFFSET(Dashboard!A$34,0,0,COUNTA(Dashboard!A$34:A$52)-4,1)
LT - =OFFSET(Dashboard!E$34,0,0,COUNT(Dashboard!E$34:E$52)-4,1)
LG - =OFFSET(Dashboard!F$34,0,0,COUNT(Dashboard!F$34:F$52)-4,1)
G - =OFFSET(Dashboard!H$34,0,0,COUNT(Dashboard!H$34:H$52)-4,2)
N - =OFFSET(Dashboard!K$34,0,0,COUNT(Dashboard!K$34:K$52)-4,8)

Axis label range: =Dashboard!Index (also tried ='Dashboard'!Index)
Series values: =Dashboard!LT (or LG, G, N)

When I'm in the Data Manager it shows the right selection whenever I click on the formula, but when I try to put it in the chart it shows different data in the chart itself.
At first my charts were in a different sheet, but I've also tried and move all the charts into the same data files as were I defined the name ranges.

The OFFSET formulas in the Name Manager also (almost all) change whenever I change the data through a drop down menu.

Anyone who knows how to solve this ?