How to change data from a chart dynamically (DESLOC + RANGE NAME)?

Question

I’m trying to create a dynamic graph where a combo changes the chart type.

I actually created a name where I added an offset formula that not only changes the range height but also varies the columns.

Explaining the process of: In the chart’s data sheet, every 2 columns have legend information and values from a different chart, something like in the attached image (Datasheet). I select the chart type in the combo and move it to the caption columns and values for selection.

Name: Rangegraficolegenda Formula: =DESLOC(Table2[[#Headers];[Months]];1;CORRESP(Data! $G$2;Data! $1:$1;0)-1;CONT.SE(Column;"<>");1)

Name: Rangegraficovalores Formula: =DESLOC(Table2[[#Headers];[Months]];1;CORRESP(Data! $G$2;Data! $1:$1;0);CONT.SE(Column;"<>");1)

Name: Column Formula: =DESLOC(Table2[[#Headers];[Months]];1;CORRESP(Data! $G$2;Data! $1:$1;0)-1;1000;1)

Notice the image that the combo is related to the G1 cell and that through a procv I search what the name of the graph in the G2 cell

Column Name only indicates which column of the chart selected to calculate the height

The problem is that when I add the names "Grafico.xls! Rangegraficovalores" and "Grafico.xls! Rangegraficolegenda" the chart does not change and when I return to the selection of data from the chart series I notice that the names disappear and a normal range appears. The curious thing is that when I do a test and add "Grafico.xls! Rangegraficovalores" in both values of the series (values and caption) everything works!

How can I make my chart vary the information?

Answer 1

I’d like you to do two things:

1. Post the spreadsheet to feel better what you want; and
2. Tell whether or not you have knowledge in Excel VBA.

From what "I understood", it will only be possible with VBA, but I will wait the access to your spreadsheet to confirm. If you are "afraid" of sharing company information, change numerical data by others, but keep some data for testing.

At.