Named ranges in Charts

ElvisSteel

Board Regular
Joined
Mar 23, 2009
Messages
122
Hi,

I have a table of data that I use to create a chart based on Location. Currently the table has 10 locations, but only 6 are currently used. The lines on the chart just drop to zero for the blank locations, and I want to use a dynamic range for this axis.
However, when I try to enter this, I get an error, and if I try to enter the dynamic range directly I also get an error.
I'm sure this must be possible, can anyone help?

Thanks
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,735
Office Version
365
Platform
Windows
Hi

Can you please be a little more specific in terms of the formula you use for the dynamic range, and the errors that you encounter?

Perhaps it has to do with the syntax of your named range reference. When referring to names in charts you need to preceed the name with the workbook name such as:

Book1!MyName
 

ElvisSteel

Board Regular
Joined
Mar 23, 2009
Messages
122
Jon,

Thanks for your help.
I was not including the workbook name in the chart, and it seems OK now

Steve
 

ElvisSteel

Board Regular
Joined
Mar 23, 2009
Messages
122
I'm still having some problems with this.
Requirement is simple (on the face of it)

I have a table which can contain up to 10 columns with a single row of data. I have a chart which is plotted from this.
If all 10 columns have headings, the chart will be based on 10 entries.
If only the first 5 columns have headings, I want the chart to just show the 5 entries and not the data without headings (always zero).
I know how to set up a dynamic range name (using Offset) to set a drop down list which dynamically adjusts according to the entries in the headings, but I am still having issues with getting a chart to work with this in the same way.
Jon pointed me towards using the full name in the reference, and I go it to work, but now I have done something and cannot get it back.

I'm sure it cannot be that difficult!

Regards

Steve
 

Forum statistics

Threads
1,082,275
Messages
5,364,178
Members
400,785
Latest member
Mahar92

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top