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

1

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?

    
asked by anonymous 14.01.2015 / 17:02

1 answer

-1

I would like you to do 2 things:

  • Post the worksheet to feel better what you want; and
  • Tell whether or not you have Excel VBA knowledge.
  • From what I understand, it will only be possible with VBA, but I will wait for access to your worksheet to confirm. If you are "afraid" to share company information, change the numeric data to others, but keep some data to test.

    At.

        
    19.01.2015 / 17:59