Dynamic Chart Titles

[Sharon]

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

[John Mansfield]

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

[Tushar Mehta]

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 <[email protected]>,
[email protected] 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
>

[Sharon]

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

[Sharon]

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 <[email protected]>,
> [email protected] 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
> >
>

[Jon Peltier]

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

[Sharon]

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