Excel Dynamic Chart

[Delita]

I am creating a chart that will be dynamically updated when a user inputs a few variables that change a data table and will be using defined Names to specify most of the the information. I am using

='Spread.xlsx'!Name

as my series the graph pulls from (which is one column out of many). However I want it to update upon a cell that a user inputs which would be a Name (group of data previously specified.)

ie

The user inputs Set3 in a cell
Set3 is a Name that specifies a group of data
Graph series is ='Spread.xlsx'!Set3

but that Set3 will have to change according to the input.


Im not sure if I explained that very thoroughly, but appreciate any input.

 

[PowerEngineer]

Here's the slick way of accomplishing it: Dynamic Charting using OFFSET

Basically, the user input ('Set3') can be used to set a cell value that is referenced in the named range using the OFFSET function to pick the right column out of the range of possibilities. The named range can also be used to define the chart. The user input effectively changes the column used in the chart.

Good luck!

 

[Delita]

Originally posted by: PowerEngineer

Here's the slick way of accomplishing it: Dynamic Charting using OFFSET

Basically, the user input ('Set3') can be used to set a cell value that is referenced in the named range using the OFFSET function to pick the right column out of the range of possibilities. The named range can also be used to define the chart. The user input effectively changes the column used in the chart.

Good luck!

I'm using the offset function to change the range of my Name as the range of the data changes, but how can I refence the change in user input in my chart series?

 

[PowerEngineer]

I'm thinking that you will set up a formula in a cell (e.g $A$2) that comes up with the column number (in the OFFSET range) that is based the user input (i.e. 'Set3'). $A$2 is then used in the definition of the (OFFSET) named range used in the chart.