View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
fruitticher fruitticher is offline
external usenet poster
 
Posts: 11
Default Using named ranges in dynamic charts (excel 2007)

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" wrote:

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" wrote:

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