Dynamic chart sheet

**Buster^** · 06-13-2013, 03:59 PM

Is there a way to make a chart sheet dynamic without using VBA? Like for example use defined name and the index formula.
For example =A1:INDEX(B1:B2;E11)

Someone made a workbook that only works for windows and not mac, so i try to figure what's wrong and how I can fix it...

**teylyn** · 06-14-2013, 04:08 AM

Strictly speaking, a "chart sheet" cannot be made dynamic. It just displays a chart that shows values from a data source.

But the data source can be made dynamic. If the chart references a dynamic range name instead of a fixed cell range, then it will update when the data that is feeding the range name changes.

Your sample range name formula is a good start. Whether or not it works depends on what value is in cell E11.

There are many ways to construct dynamic range names. The perfect formula for your need can only be determined if you share your requirements, preferably as a sample file with dummy data, not real names or addresses.

**Buster^** · 06-15-2013, 11:07 AM

Original.xlsSo the workbook in the attachments is the one I am talking about, now the VBA is used for updating the charts, but I would much rather preffered that it does that by not using VBA or at least mak it compatible with Mac. Because I know that the buttons won't work with mac, and I hav tried to fix it, but that's not the main problem. If the charts cna be updated by something else than VBA it would be great! (All the cells that are green are user input, also cell D11 is the count for how many rows)
The rest of the code/buttons you don't have to think about. The chart sheets are "Enhetsdiagram" and "Totaldiagram".

(This is in norwegian, so if any questions please post or send me a message)

**-AJ-** · 06-16-2013, 06:22 PM

Either:
A) Use dynamic named ranges (either Index method, or offset method. See below)

OR

B) Covert your data to a table and use structured referencing rather than referencing cell ranges. I.e Use the table column headers instead of A1:A100 etc. As your table expands, the charts should update. (I think)

Index method:

Formula:

Please Login or Register  to view this content.

Offset method:

Formula:

Please Login or Register  to view this content.

Replace COUNT with COUNTA for non-numeric data

**Palmetto** · 06-16-2013, 10:01 PM

Easiest way: convert the data source into an Excel Table feature, then, with the table selected, create a Pivot Table/Chart.
With this approach there is no need for named ranges or VBA.

Optionally, seeing as you have Excel 2010/2013, you can create slicers as controls to filter the chart.
Pivot tables do require a refresh everytime changes are made to the source data, but this is a simple button click which, if you like, may be automated with a few lines of VBA code.

Do some searching on:
Excel 2010 Tables
Excel Pivot Charts / Slicers

**teylyn** · 06-17-2013, 06:38 AM

Some Excel features like Tables, new Pivot Table features like Slicers, etc, will not be available if the file is run in compatibility mode. You need to decide: do you run 2010/2013? If so, convert the file to the new version and benefit from the new features.

If you run the file in compatibility mode, your options will be rather limited, and tables and slicers will not be available at all.

If you have 2010 or newer, what is the rationale for using an *.xls file?

cheers, teylyn

**Buster^** · 06-18-2013, 11:20 AM

Hmm, the spreadsheet was made before the .xlsx and therefore it has that .xls format (Note: the spreadsheet was not made by me, I am just trying to work it out). But as most people (pupils have excel 2010 or excel 2011(mac) there is no need for compatibility mode

Edit*if need here is the spreadsheet in new format: Original2.xlsm

Just a quick question, how can I do it so that 0 is not displayed in a chart?

**Buster^** · 06-18-2013, 05:37 PM

So after so time spending, if found a way to do this, using the old NA trick with :D

If anyone knows a good excel book that talks (primarily or only) about charts, please send me a PM, think I need to learn a bit more (I have changed to excel 2013 so, it would be nice if the book was made for excel 2013, due to new layout system...)