Dynamic chart with Named ranges

[mrxlsx]

Hi all,
I have difficulty in building OFFSET formula to access my chart data dynamically. My data layout is as below.



[TABLE="width: 500"]
<tbody>[TR]
[TD]State
[/TD]
[TD]FL
[/TD]
[TD]AZ
[/TD]
[TD]TX
[/TD]
[TD]ID
[/TD]
[/TR]
[TR]
[TD]Qty
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[/TR]
[TR]
[TD]Sales
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[/TR]
[TR]
[TD]Cost
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[/TR]
[TR]
[TD]Profit
[/TD]
[TD]xx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[/TR]
</tbody>[/TABLE]

If I select any State in the cell A8, I should get the chart highlighting with all the Qty, Sales, Cost, Profit etc with the State name as the chart title. If I change the State in the drop down, data should change accordingly.


I tried to use OFFSET with the combination of some Named ranges, but some how I am not clear on how to link the data to chart as well as building the correct formula.

Please help me. Thanks everybody.

InteractiveChart - Download - 4shared - farida perveen

mrxlsx

 

[Chris Mack]

If your data starts in cell Sheet1!A1, define the following names:

"Values" =

=OFFSET(Sheet1!$A$1,1,MATCH(Sheet1!$A$8,Sheet1!$1:$1,0)-1,4,1)

"Title" =

=OFFSET(Sheet1!$A$1,0,MATCH(Sheet1!$A$8,Sheet1!$1:$1,0)-1,1,1)

Then use these in your chart.

The chart title will be:

Sheet1!Title

The series values (y values) will be:

Sheet1!Values

Those references will actually default to the name of your workbook (if you set the names' Scope to 'Workbook') but you can still input them like that (or you could use, e.g., "Book1!Title").

Note: You could also simply set the chart title as "=$A$8".

Hope this helps,

Chris.

 

[mrxlsx]

Hi Chris,

Thanks for the effort !!

Even I built the same formula and I posted here just to make sure mine is right. But somehow the OFFSET formula is not updating when I change the selection choice. The prescribed formula is showing the values for all the values for all the states. What I want to see in the chart is the values for Qty, Sales, Cost, Profit only for the selected state and not for other states. Anything am I missing in the cooking process??

mrxlsx

 

[Chris Mack]

My formula does precisely that.

What formula are you using?

Chris

 

[mrxlsx]

Chris,

Sorry for bothering you. The same formula I am using, but charts were showing up each and every item...meaning all the value for all the states which is not my requirement. Are they working according to my requirement at your end? Can you give a hint of the process of linking and naming the cells or else post the finished Excel sheet's link so that much of the explanation part can be avoided. We will see if something goes wrong later.

Thank you very much for all your effort man!
mrxlsx

 

[Chris Mack]

Well, I basically started by selecting A1:B5 and inserting a column chart.

You might need to switch the data (button in the middle of 'Select Data') so that A2:A5 are on the right-hand side. Then I just applied the names to the series on the left.

Here's a link to an example workbook:

https://app.box.com/s/3h1d4svrntexwp3tt8f8

Hope this helps,

Chris.

 

[mrxlsx]

Chris,

At last it's working fine. I have taken this trick to the next level with my office data. I work on 32 columnar data seamlessly. It's just working fine. The only problem I have faced at is linking them to the chart which was corrected by your instructional steps. Thank you very much.

mrxlsx

 

[Chris Mack]

Yw!