Dynamic Chart Titles

[Guest]

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/newsgroups/dynamic_charts/index.html

TIA,

Sharon

 

[Guest]

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.

 

[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

 

[Guest]

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

 

[Guest]

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

 

[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/
_______

 

[Guest]

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