Home 
Search 
Today's Posts 
#1




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 leftclick 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 
#2




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




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




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




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




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




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 reopening 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 renamed the ranges with shorter names in Name Manager. The formulas were not immediately visible but after saving and reopening 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




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 reopening 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 renamed the ranges with shorter names in Name Manager. The formulas were not immediately visible but after saving and reopening 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




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 reopening 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 renamed the ranges with shorter names in Name Manager. The formulas were not immediately visible but after saving and reopening 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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Named ranges in Excel 2007  Excel Discussion (Misc queries)  
dynamic ranges in excel 2007  Charts and Charting in Excel  
Excel 2007 BetaNamed Ranges  Excel Discussion (Misc queries)  
Dynamic Named Ranges  Charts and Charting in Excel  
of Named Ranges, Dynamic Charts and scroll bars...  Charts and Charting in Excel 