Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Dynamic Chart Titles
Hello,
I am having a problem with a dynamic chart title and saving, closing and then re-opening the workbook and the chart title still being dynamic. On Sheet2 of my workbook I have some drop-down menus in cells B2:E2. On Sheet7 of my workbook in cells BA1:BA3 I have Concatenation formulas that I want to use as my Chart titles. For example: =CONCATENATE("This is my " Sheet2!$B$2, " ", "chart.") I named the ranges on Sheet7, BA1 is named "FAC", BA2 is named "POD" and BA3 is named "NAF". I then added a text box to my first chart where the chart title should go. In the text box I put the formula =FAC. Then I go back to Sheet2 change my options in my drop down menus, come back to Sheet7 to make sure it worked and it does. But, when I save the workbook, close it and re-open it, the text box seems to "forget" the range that I had put in there and I have to do it all over again. How do I make the text box "remember" from save to save and open - close - re-open? This is the web site I used as a guide to accomplish this: http://www.tushar-mehta.com/excel/ne...rts/index.html TIA, Sharon |
#2
|
|||
|
|||
Sharon,
I'm speculating but I think the problem might be due to multiple sheet references. Try adding a cell reference on the sheet with the embedded chart that refers to the named reference. For example, if your chart is on Sheet 1 and you want your dynamic title to refer to the named reference called FAC, in Sheet1!A1 add the formula =FAC. Then, change your dynamic title formula to read =$A$1. ---- Regards, John Mansfield http://www.pdbook.com "Sharon" wrote: Hello, I am having a problem with a dynamic chart title and saving, closing and then re-opening the workbook and the chart title still being dynamic. On Sheet2 of my workbook I have some drop-down menus in cells B2:E2. On Sheet7 of my workbook in cells BA1:BA3 I have Concatenation formulas that I want to use as my Chart titles. For example: =CONCATENATE("This is my " Sheet2!$B$2, " ", "chart.") I named the ranges on Sheet7, BA1 is named "FAC", BA2 is named "POD" and BA3 is named "NAF". I then added a text box to my first chart where the chart title should go. In the text box I put the formula =FAC. Then I go back to Sheet2 change my options in my drop down menus, come back to Sheet7 to make sure it worked and it does. But, when I save the workbook, close it and re-open it, the text box seems to "forget" the range that I had put in there and I have to do it all over again. How do I make the text box "remember" from save to save and open - close - re-open? This is the web site I used as a guide to accomplish this: http://www.tushar-mehta.com/excel/ne...rts/index.html TIA, Sharon |
#4
|
|||
|
|||
Thanks for the suggestion. If I'm understanding what you're saying, I think
that's what I'm currently doing. My chart title reference (=FAC) is on the same sheet as the chart. I will try just saying =$A$1 and see if that makes a difference. Thanks for your input. Sharon "John Mansfield" wrote: Sharon, I'm speculating but I think the problem might be due to multiple sheet references. Try adding a cell reference on the sheet with the embedded chart that refers to the named reference. For example, if your chart is on Sheet 1 and you want your dynamic title to refer to the named reference called FAC, in Sheet1!A1 add the formula =FAC. Then, change your dynamic title formula to read =$A$1. ---- Regards, John Mansfield http://www.pdbook.com "Sharon" wrote: Hello, I am having a problem with a dynamic chart title and saving, closing and then re-opening the workbook and the chart title still being dynamic. On Sheet2 of my workbook I have some drop-down menus in cells B2:E2. On Sheet7 of my workbook in cells BA1:BA3 I have Concatenation formulas that I want to use as my Chart titles. For example: =CONCATENATE("This is my " Sheet2!$B$2, " ", "chart.") I named the ranges on Sheet7, BA1 is named "FAC", BA2 is named "POD" and BA3 is named "NAF". I then added a text box to my first chart where the chart title should go. In the text box I put the formula =FAC. Then I go back to Sheet2 change my options in my drop down menus, come back to Sheet7 to make sure it worked and it does. But, when I save the workbook, close it and re-open it, the text box seems to "forget" the range that I had put in there and I have to do it all over again. How do I make the text box "remember" from save to save and open - close - re-open? This is the web site I used as a guide to accomplish this: http://www.tushar-mehta.com/excel/ne...rts/index.html TIA, Sharon |
#5
|
|||
|
|||
Well I'm glad to know I'm not the only one and that it's not me doing
something wrong. Thankfully nothing crashes. :) Thanks for your response, Sharon "Tushar Mehta" wrote: Don't have an immediate solution for you, but I ran into a similar problem -- actually worse -- as you. I created a drop down combo box in a chart on its own sheet. The combo box has a source range of =TeamList, where TeamList is a range on a worksheet. The chart and combo box work fine when created. After saving, closing, and reopening the file, the combo box is no longer linked to TeamList. Worse, if I try and reestablish the list, XL crashes. Happens consistently on XL2003. Haven't had time to research the issue further. -- Regards, Tushar Mehta www.tushar-mehta.com Multi-disciplinary business expertise + Technology skills = Optimal solution to your business problem Recipient Microsoft MVP award 2000-2005 In article , says... Hello, I am having a problem with a dynamic chart title and saving, closing and then re-opening the workbook and the chart title still being dynamic. On Sheet2 of my workbook I have some drop-down menus in cells B2:E2. On Sheet7 of my workbook in cells BA1:BA3 I have Concatenation formulas that I want to use as my Chart titles. For example: =CONCATENATE("This is my " Sheet2!$B$2, " ", "chart.") I named the ranges on Sheet7, BA1 is named "FAC", BA2 is named "POD" and BA3 is named "NAF". I then added a text box to my first chart where the chart title should go. In the text box I put the formula =FAC. Then I go back to Sheet2 change my options in my drop down menus, come back to Sheet7 to make sure it worked and it does. But, when I save the workbook, close it and re-open it, the text box seems to "forget" the range that I had put in there and I have to do it all over again. How do I make the text box "remember" from save to save and open - close - re-open? This is the web site I used as a guide to accomplish this: http://www.tushar-mehta.com/excel/ne...rts/index.html TIA, Sharon |
#6
|
|||
|
|||
Sharon -
You can't use just the name of the name (ha ha) in the chart's text element formulas. Or the address. You need to qualify the name or the address with the sheet name: =Sheet7!FAC or =Sheet7!$BA$1 If you type your = sign and select a cell, if the cell isn't specially named, Excel puts the sheet reference in the formula. If the cell has a name, Excel puts just the unqualified name into the formula and throws an error (Excel 2000 anyway; my modern computer's in the sickbay). Don't feel so bad about not knowing, because Excel doesn't even get it right. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Sharon wrote: Hello, I am having a problem with a dynamic chart title and saving, closing and then re-opening the workbook and the chart title still being dynamic. On Sheet2 of my workbook I have some drop-down menus in cells B2:E2. On Sheet7 of my workbook in cells BA1:BA3 I have Concatenation formulas that I want to use as my Chart titles. For example: =CONCATENATE("This is my " Sheet2!$B$2, " ", "chart.") I named the ranges on Sheet7, BA1 is named "FAC", BA2 is named "POD" and BA3 is named "NAF". I then added a text box to my first chart where the chart title should go. In the text box I put the formula =FAC. Then I go back to Sheet2 change my options in my drop down menus, come back to Sheet7 to make sure it worked and it does. But, when I save the workbook, close it and re-open it, the text box seems to "forget" the range that I had put in there and I have to do it all over again. How do I make the text box "remember" from save to save and open - close - re-open? This is the web site I used as a guide to accomplish this: http://www.tushar-mehta.com/excel/ne...rts/index.html TIA, Sharon |
#7
|
|||
|
|||
Wow! I really hope this works. I am working at home right now on a Mac and
it seems to "remember" now when I added the Sheet reference in front of the cell reference. I'll test it again on Monday when I get to work and am on a PC just to make sure. Thanks so much, I think I'm beginning to see a light at the end of the tunnel. :) Sharon "Jon Peltier" wrote: Sharon - You can't use just the name of the name (ha ha) in the chart's text element formulas. Or the address. You need to qualify the name or the address with the sheet name: =Sheet7!FAC or =Sheet7!$BA$1 If you type your = sign and select a cell, if the cell isn't specially named, Excel puts the sheet reference in the formula. If the cell has a name, Excel puts just the unqualified name into the formula and throws an error (Excel 2000 anyway; my modern computer's in the sickbay). Don't feel so bad about not knowing, because Excel doesn't even get it right. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Sharon wrote: Hello, I am having a problem with a dynamic chart title and saving, closing and then re-opening the workbook and the chart title still being dynamic. On Sheet2 of my workbook I have some drop-down menus in cells B2:E2. On Sheet7 of my workbook in cells BA1:BA3 I have Concatenation formulas that I want to use as my Chart titles. For example: =CONCATENATE("This is my " Sheet2!$B$2, " ", "chart.") I named the ranges on Sheet7, BA1 is named "FAC", BA2 is named "POD" and BA3 is named "NAF". I then added a text box to my first chart where the chart title should go. In the text box I put the formula =FAC. Then I go back to Sheet2 change my options in my drop down menus, come back to Sheet7 to make sure it worked and it does. But, when I save the workbook, close it and re-open it, the text box seems to "forget" the range that I had put in there and I have to do it all over again. How do I make the text box "remember" from save to save and open - close - re-open? This is the web site I used as a guide to accomplish this: http://www.tushar-mehta.com/excel/ne...rts/index.html TIA, Sharon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to change range for dynamic chart in excel 2000 with button? | Charts and Charting in Excel | |||
dynamic chart with scroll bar | Excel Discussion (Misc queries) | |||
Centering Axis and Chart Titles on chart | Excel Discussion (Misc queries) | |||
Dynamic charting problems (events) | Charts and Charting in Excel | |||
Creating a dynamic chart | Charts and Charting in Excel |