• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Interactive range names stressing me out!

A

Alexis

Member
Hello all, happy new year!

I apologise in advance that I haven't upload a file, it's got loads of confidential work data in and haven't created a dummy copy at this stage.

Here's the scenario.

I've created a dashboard with some charts that allow the user to select the how many years data to view with check boxes.
The check boxes link to a lookup page which show which month to start at and how many months to include.
These references are used in the range name along with offset. This is so that only the chosen data fits the chart.
These work fine. When the check boxes are change the range changes accordingly.

I'm using Ron Person's very good book, "Balanced Scorecards and Operational Dashboards with Excel" as guideance.
In the book it says that when clicking on a series in a chart that you need to manually type in the workbook name and the named range. Example:

=SERIES("Series Name",Example.xlsx!rngNumberOfMonths,Example.xlsx!rngData,1)

Here's the problem.

If the workbook has a very short name, like Data.xlsx, it seems to be fine and it works. If however my workbook is called, say something something blah blah blah.xlsx then when I hit enter after manually typing in the new range names for another series, excel does nothing. It won't allow me to enter and I have to escape out of the formula bar.
I've tried adding the ranges while it's called Data.xls then changing it back to it's correct (longer) name and it works, but then if I want to amend any charts, it won't let me do it!!!

I hope I've explained myself at least half reasonably. I'm so frustrated!!!

Please can somebody help me?! Or at least tell me not to bother posting unless I upload a file?!?!?!

Thanks in advance

Alexis
 
=SERIES("Series Name",'something something blah blah blah.xlsx'!rngNumberOfMonths,'something something blah blah blah.xlsx'!rngData,1)

  • Try to enclose the file name with (')
 
Thanks both for your tips. Debraj - my mistake, a typo on the message, my formula in the workbook did have the (') marks around the file name.

Just a thought, and this might sound really weird, but does Excel not like named ranges that begin with lowercase characters?!
I've just tried again in a workbook (with a long file name so out goes that theory) the following range names:
  • chtMonths
  • chtTest
neither worked

  • Months
  • Test
both worked!

Surely that can't be the answer can it?

I did try a couple of name variations and both times the same thing happend...
 
Alexis,

Another thing to consider is the scope of the named range. By default, these will be workbook scope. However, if you make a copy of a sheet with named ranges, the new sheet will have the same same names with a workSHEET scope. You might have a look in the name manager (CTRL+F3) and see if that is the case. You may need a worksheet name added to the reference in that case.

Regards,
Ken
 
Hi Alexis ,

You cannot use a name in a Series formula which begins with the letter c or the letter r ; at least that is what it looks like.

I created three named ranges chtMonth , htMonth and rhtMonth , all of them referring to the same worksheet range , and tried using each of them in a SERIES formula ; Excel accepts htMonth , but does not accept the other two. Other letters may be prohibited too ; you can try.

You will find some more information in this link :

http://dailydoseofexcel.com/archives/2009/04/07/illegal-range-names/

Narayan
 
Thanks all for your repsonses.

Narayan - thanks - it looks as though the 'c' at the beginning was the culprit.

Problem solved :)
 
You must log in or register to reply here.
Back
Top