I'm trying to create a dynamic chart where a combo changes the chart type.
In fact I created a name where I added a formula that not only changes the height of the range but also changes the columns.
Explaining the process: In the chart data sheet, every 2 columns I have caption information and values of a different chart, something like in the attached image (Data Sheet). I select the graph type in the combo and the shift goes to the legend columns and values referring to the selection.
Name: RangeGraficoLegenda Formula: = OFF (Table2 [[# Headers]; [Months]]; 1; CORRESP (Data! $ G $ 2; Data! $ 1: $ 1; 0) -1; CONT.SE (Column; "< > ); 1)
Name: RangeGraficoValores Formula: = OFF (Table2 [[# Headers]; [Months]]; 1; CORRESP (Data! $ G $ 2; Data! $ 1: $ 1; 0); CONT.SE (Column; "< >"); 1)
Name: Column (For example, in the form of a table), we can use the following formula: = DESLOC (Table2 [[# Headers]; [Months]]; 1. CORRESP (Data! $ G $ 2; Data! $ 1: $ 1; 0) -1; 1000; 1)
Look at the image that the combo is related to cell G1 and that through a procv I look for the name of the graphic in cell G2
The Column Name only indicates which column of the selected graph to calculate the height
The problem is that when I add the names "Graphics.xls! RangeGraficoValores" and "Graphics.xls! RangeGraficoLegenda" the graph does not change and when I go back to the data selection of the graphic series I notice that the names disappear and appear range. The curious thing is that when I do a test and add the "Graphics.xls! RangeGraficoValores" in both values of the series (values and legend) everything works!
How can I make my graph vary the information?