Using named ranges in dynamic charts (excel 2007)

[fruitticher]

Excel 2007. Working with a dynamic chart, using named ranges. A couple of
challenges for someone clever...

1. The first formula works as a data series, the second will not. When I
type or paste the second into the forumla bar for the chart, it will not let
me enter out. No error display either. It's like there's a problem with the
formula. However, the named ranges seem to be valid, since I've used them
elsewhere. So what's the difference?

a. =SERIES('Yearly Analysis'!$A$4,'WFG
Finances.xlsx'!TotRevenueYTDlabels,'WFG Finances.xlsx'!TotRevenueYTDvalues,1)

b. =SERIES('Yearly Analysis'!$A$4,'WFG
Finances.xlsx'!CostGoodsSoldYTDlabels,'WFG
Finances.xlsx'!CostGoodsSoldYTDvalues,1)

Both are exactly alike except for the names of the ranges. These are valid
ranges (I can use them for other things) and they are al scoped for the
entire workbook.

2. This second question is even more tricky. And hard to describe. See
formula a. above in question #1. I can create a chart adn type or paste this
formula in the formula bar to create a series on the chart. If I then
left-click on the graphical display of the data series (say a line on a line
chart) then the formula will reappear in the formula bar. HOWEVER...if I save
and close the workbook, then open it back up, I can never get the formula to
display again, by clicking on the data series or by any other method. Can
anyone think of why?? This is important because I need to be able to come
back later and see the formula used. This is just plain weird. And in the
newly opened workbook if I then create another chart and formula, then THAT
new formula will display whenever I click on the data series, but only until
I save and close the workbook. Once I open it up again, then that new formula
will no longer display either.

???

Thanks for any help.

fruitticher

 

[OssieMac]

Hi,

Without actually testing, I wonder if your problem might be in Yearly
Analysis'!$A$4. This is a reference to an individual cell which contains the
header which becomes the series name. Each series should have a different
column header/Name and hense should not be the same reference.

Checked your other problem with some of my charts and I can always see the
formula.

Regards,

OssieMac

 

[fruitticher]

OssieMan,

Thanks for checking.

Problem doesn't seem to be that Yearly Analysis reference because it won't
even work in a new chart by itself as the only series.

Regarding the disappearing formulas, are you sure you're closing the file
and reopening to check? I can see them all day long until I do that. Then
they vanish.

Regards,
fruitticher

 

[OssieMac]

Hi Fruitticher,

Not only did I save and close the workbooks, I closed Xl also. That was
yesterday and the computer was off overnight and I checked again today and
everything is still fine. I don't know if the OS makes a difference. I'm
using Windows Vista Home with Office Professional 2007. Also, do you
experience the problem with all of your workbooks?

I don't think that I can provide any more suggestions that you couldn't
arrive at yourself.

Regards,

OssieMac

 

[fruitticher]

OssieMac,

Strange. I created a brand new workbook, created some named ranges and a
chart. I can open and close that workbook all day long and have no problem
seeing the formulas when clicking on the data series.

But the original workbook still has the same issues.

???

Very odd.

Thanks for all your help!

fruitticher

 

[fruitticher]

OssieMac,

Although I still can't figure out why my series formulas are disappearing in
this particular worksheet, I did figure out why I was unable to enter in the
b. formula in my original question #1.

Apaprently, defined names cannot begin with 'C' or 'R', either lower or
upper case. I was naming my ranges 'CostGoodsSoldYTDvales' and Excel was
allowing that name to be created, but then it never would work in the
SERIES() formula. Strangly I could get it to work in other things.

Anyway, FYI.

fruitticher

 

[OssieMac]

Hi again Fruitticher,

I did some testing and found some problems like you described. I think that
I have overcome all of them by shortening the names for the named ranges.

I copied the named ranges you posted and I experienced similar problems
trying to insert them in the formula bar. The first one like you said worked
but the second one would not. On saving, closing and re-opening I had lost
the display of the formula on the formula bar.

I started again with a new worksheet and used shorter names for the named
ranges and it appeared to overcome all the problems.

I then went back to the workbook where the formulas would not display and
re-named the ranges with shorter names in Name Manager. The formulas were not
immediately visible but after saving and re-opening the formulas became
visible. I was then able to insert the new shorter names where previously I
could not.

Also I'm sure that I have seen a message at times something like "the range
is too complex to display" when accessing the ranges by right clicking on the
series and selecting 'Select Data' but I can't replicate that at the moment.

I'll be interested in any further comments that you or anyone else has on
the subject.

Regards,

OssieMac

 

[fruitticher]

OssicMac,

That's excellent information! Thanks for taking the time.

Yesterday I tried to recreate the spreadsheet, thinking that maybe there was
merely something corrupt in a cell or named range somewhere. But then I spent
all afternoon trying to figure out an error I kept getting about a missing
link to a workbook that never existed. This error only came up after I
created the named range for the labels of my data. No matter the name of the
range, as soon as I created this range and saved/closed the file, upon
reopening I would get the missing link notification. So that got me off track
chasing that down. Never found out what it was though. I had copied a bunch
of cells over and althought I can't find anything I suspect there's something
in a formula somewhere that this new workbook is misinterpreting.

But now I will go back to the original workbook and shorten my range names.

There's always something, isn't there?

Thanks again for all your legwork. Sure does help!

Regards,
fruitticher

 

[fruitticher]

OssieMac,

Well, the saga continues. I tried shortening the names to 'tlab' and 'tval'
but my formula never shows up in the original workbook. I even deleted all
the other ranges, all charts, saved, reopened, created a brand new chart with
the shortened name formula. Then saved, reopened, and no formulas. Below is
the series formula:

=SERIES('TotRev'!$J$4,'WFG Finances.xlsx'!tlab,'WFG Finances.xlsx'!tval,1)

I even changed the name of the worksheet to be shorter with no spaces. Then
even renamed the entire workbook to something short with no spaces.

The workbook had a password to open, and I even removed that to no avail.

There's something in there somewhere it doesn't like. I'm going to keep
playing with it. Will let you know what I find.

fruitticher