A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Using named ranges in dynamic charts (excel 2007)



 
 
Thread Tools Display Modes
  #1  
Old September 14th 07, 10:56 PM posted to microsoft.public.excel.worksheet.functions
fruitticher
external usenet poster
 
Posts: 11
Default Using named ranges in dynamic charts (excel 2007)

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
Ads
  #2  
Old September 15th 07, 08:12 AM posted to microsoft.public.excel.worksheet.functions
OssieMac
external usenet poster
 
Posts: 2,510
Default Using named ranges in dynamic charts (excel 2007)

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

  #3  
Old September 15th 07, 02:38 PM posted to microsoft.public.excel.worksheet.functions
fruitticher
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

  #4  
Old September 15th 07, 11:58 PM posted to microsoft.public.excel.worksheet.functions
OssieMac
external usenet poster
 
Posts: 2,510
Default Using named ranges in dynamic charts (excel 2007)

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

> 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

  #5  
Old September 18th 07, 03:08 PM posted to microsoft.public.excel.worksheet.functions
fruitticher
external usenet poster
 
Posts: 11
Default Using named ranges in dynamic charts (excel 2007)

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

"OssieMac" wrote:

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

  #6  
Old September 18th 07, 04:54 PM posted to microsoft.public.excel.worksheet.functions
fruitticher
external usenet poster
 
Posts: 11
Default Using named ranges in dynamic charts (excel 2007)

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

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

  #7  
Old September 18th 07, 11:40 PM posted to microsoft.public.excel.worksheet.functions
OssieMac
external usenet poster
 
Posts: 2,510
Default Using named ranges in dynamic charts (excel 2007)

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

  #8  
Old September 19th 07, 04:18 PM posted to microsoft.public.excel.worksheet.functions
fruitticher
external usenet poster
 
Posts: 11
Default Using named ranges in dynamic charts (excel 2007)

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

"OssieMac" wrote:

> 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
>

  #9  
Old September 19th 07, 04:56 PM posted to microsoft.public.excel.worksheet.functions
fruitticher
external usenet poster
 
Posts: 11
Default Using named ranges in dynamic charts (excel 2007)

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

"OssieMac" wrote:

> 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
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Named ranges in Excel 2007 [email protected] Excel Discussion (Misc queries) 2 September 10th 07 08:22 PM
dynamic ranges in excel 2007 onzilla Charts and Charting in Excel 6 April 24th 07 12:41 PM
Excel 2007 Beta-Named Ranges reno Excel Discussion (Misc queries) 4 June 26th 06 09:33 PM
Dynamic Named Ranges [email protected] Charts and Charting in Excel 0 March 9th 06 03:09 PM
of Named Ranges, Dynamic Charts and scroll bars... z.entropic Charts and Charting in Excel 2 May 20th 05 07:16 PM


All times are GMT +1. The time now is 03:58 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.