Hello all,

I have 2 sheets, "Data Sheet" and "Chart Sheet". Data Sheet contains data
to create a bar chart in the Chart Sheet, including the data for bar chart
title. I have no problem creating the bar chart using the data in the Data
Sheet.

I am just wondering whether I can make the chart title dynamic, which means
whenever the value for chart title in the Data Sheet changes, the chart title
will change.

In addition, I want to insert an area on the bar chart for the Source
information. I also want the source information dynamic from the value from
the Data Sheet.

Can they be done?

Thanks.

Hi,

Here are instruction on how to make the title text dynamic,
http://www.andypope.info/tips/tip001.htm

The same trick can be used for a textbox embedded in the chart.

Cheers
Andy

AccessHelp wrote:
Hello all,

I have 2 sheets, "Data Sheet" and "Chart Sheet". Data Sheet contains data
to create a bar chart in the Chart Sheet, including the data for bar chart
title. I have no problem creating the bar chart using the data in the Data
Sheet.

I am just wondering whether I can make the chart title dynamic, which means
whenever the value for chart title in the Data Sheet changes, the chart title
will change.

In addition, I want to insert an area on the bar chart for the Source
information. I also want the source information dynamic from the value from
the Data Sheet.

Can they be done?

Thanks.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

I am just wondering whether I can make the chart title dynamic, which
means
whenever the value for chart title in the Data Sheet changes, the chart
title
will change.

Put the cursor in the Title
In the inputbar type an =
with the cursor still in the Input bar after the =, select the cell (swithch
sheet if necessary)

In addition, I want to insert an area on the bar chart for the Source
information. I also want the source information dynamic from the value
from
the Data Sheet.

The source address is in the Series formula (select the series)

If you really want the address to show on the chart, enter the address as a
string in some cell, perhaps a pair of =Address() worksheet functions. Add a
textbox to the chart. Use the same method as described above for the Title
to place the cell contents in the textbox. Not sure what you want but FWIW
you could also add address's to Axis titles with the =Cell method.

Regards,
Peter T

Peter,

Thanks for your help. I tried the way you instructed, and it worked.

However, there is a twist. I want to make my chart title (and text box)
both static and dynamic, which means a portion of my chart title is static
and the other portion is dynamic (e.g. Student Name: John Dole). "Student
Name" would be static, and John Dole would be dynamic from Cell A1 of Data
Sheet.

When I tried to input the formulas =concatenate("Student Name: ", 'Data
Sheet'!A1), I got an error "That Function is not valid.".

Is it possible to have a chart title and text box static and dynamic?

Thanks.

"Peter T" wrote:

I am just wondering whether I can make the chart title dynamic, which
means
whenever the value for chart title in the Data Sheet changes, the chart
title
will change.

Put the cursor in the Title
In the inputbar type an =
with the cursor still in the Input bar after the =, select the cell (swithch
sheet if necessary)

In addition, I want to insert an area on the bar chart for the Source
information. I also want the source information dynamic from the value
from
the Data Sheet.

The source address is in the Series formula (select the series)

If you really want the address to show on the chart, enter the address as a
string in some cell, perhaps a pair of =Address() worksheet functions. Add a
textbox to the chart. Use the same method as described above for the Title
to place the cell contents in the textbox. Not sure what you want but FWIW
you could also add address's to Axis titles with the =Cell method.

Regards,
Peter T

Andy,

Thanks for your help. I tried the way you instructed, and it worked.

However, there is a twist. I want to make my chart title (and text box)
both static and dynamic, which means a portion of my chart title is static
and the other portion is dynamic (e.g. Student Name: John Dole). "Student
Name" would be static, and John Dole would be dynamic from Cell A1 of Data
Sheet.

When I tried to input the formulas =concatenate("Student Name: ", 'Data
Sheet'!A1), I got an error "That Function is not valid.".

Is it possible to have a chart title and text box static and dynamic?

Thanks.

"Andy Pope" wrote:

Hi,

Here are instruction on how to make the title text dynamic,
http://www.andypope.info/tips/tip001.htm

The same trick can be used for a textbox embedded in the chart.

Cheers
Andy

AccessHelp wrote:
Hello all,

I have 2 sheets, "Data Sheet" and "Chart Sheet". Data Sheet contains data
to create a bar chart in the Chart Sheet, including the data for bar chart
title. I have no problem creating the bar chart using the data in the Data
Sheet.

I am just wondering whether I can make the chart title dynamic, which means
whenever the value for chart title in the Data Sheet changes, the chart title
will change.

In addition, I want to insert an area on the bar chart for the Source
information. I also want the source information dynamic from the value from
the Data Sheet.

Can they be done?

Thanks.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

On Sun, 17 Feb 2008, in microsoft.public.excel.charting,
AccessHelp said:
However, there is a twist. I want to make my chart title (and text box)
both static and dynamic, which means a portion of my chart title is static
and the other portion is dynamic (e.g. Student Name: John Dole). "Student
Name" would be static, and John Dole would be dynamic from Cell A1 of Data
Sheet.

When I tried to input the formulas =concatenate("Student Name: ", 'Data
Sheet'!A1), I got an error "That Function is not valid.".

Lateral Thinking time: if formulas aren't possible in the Chart box
(they aren''t, only a simple cell reference) then have the chart box
refer to a cell which is itself a formula.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

In the cell that your title/textbox links to, use a formula to construct the
static/dynamic text you want to display:

="Student Name: "&A1

then link to this cell as before.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"AccessHelp" wrote in message
...
Peter,

Thanks for your help. I tried the way you instructed, and it worked.

However, there is a twist. I want to make my chart title (and text box)
both static and dynamic, which means a portion of my chart title is static
and the other portion is dynamic (e.g. Student Name: John Dole). "Student
Name" would be static, and John Dole would be dynamic from Cell A1 of Data
Sheet.

When I tried to input the formulas =concatenate("Student Name: ", 'Data
Sheet'!A1), I got an error "That Function is not valid.".

Is it possible to have a chart title and text box static and dynamic?

Thanks.

"Peter T" wrote:

I am just wondering whether I can make the chart title dynamic, which
means
whenever the value for chart title in the Data Sheet changes, the chart
title
will change.

Put the cursor in the Title
In the inputbar type an =
with the cursor still in the Input bar after the =, select the cell
(swithch
sheet if necessary)

In addition, I want to insert an area on the bar chart for the Source
information. I also want the source information dynamic from the value
from
the Data Sheet.

The source address is in the Series formula (select the series)

If you really want the address to show on the chart, enter the address as
a
string in some cell, perhaps a pair of =Address() worksheet functions.
Add a
textbox to the chart. Use the same method as described above for the
Title
to place the cell contents in the textbox. Not sure what you want but
FWIW
you could also add address's to Axis titles with the =Cell method.

Regards,
Peter T

Jon,

Thanks for your response. I thought about that, and I was hoping that I
don't have to take that route.

I have a few text boxes, and the static values are different for each text
box. However, the dynamic value is the same for all text boxes.

Thanks again.

"Jon Peltier" wrote:

In the cell that your title/textbox links to, use a formula to construct the
static/dynamic text you want to display:

="Student Name: "&A1

then link to this cell as before.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"AccessHelp" wrote in message
...
Peter,

Thanks for your help. I tried the way you instructed, and it worked.

However, there is a twist. I want to make my chart title (and text box)
both static and dynamic, which means a portion of my chart title is static
and the other portion is dynamic (e.g. Student Name: John Dole). "Student
Name" would be static, and John Dole would be dynamic from Cell A1 of Data
Sheet.

When I tried to input the formulas =concatenate("Student Name: ", 'Data
Sheet'!A1), I got an error "That Function is not valid.".

Is it possible to have a chart title and text box static and dynamic?

Thanks.

"Peter T" wrote:

I am just wondering whether I can make the chart title dynamic, which
means
whenever the value for chart title in the Data Sheet changes, the chart
title
will change.

Put the cursor in the Title
In the inputbar type an =
with the cursor still in the Input bar after the =, select the cell
(swithch
sheet if necessary)

In addition, I want to insert an area on the bar chart for the Source
information. I also want the source information dynamic from the value
from
the Data Sheet.

The source address is in the Series formula (select the series)

If you really want the address to show on the chart, enter the address as
a
string in some cell, perhaps a pair of =Address() worksheet functions.
Add a
textbox to the chart. Use the same method as described above for the
Title
to place the cell contents in the textbox. Not sure what you want but
FWIW
you could also add address's to Axis titles with the =Cell method.

Regards,
Peter T

Del,

Thanks for your response. I thought about that, and I was hoping that I
don't have to take that route.

I have a few text boxes, and the static values are different for each text
box. However, the dynamic value is the same for all text boxes.

Thanks again.

"Del Cotter" wrote:

On Sun, 17 Feb 2008, in microsoft.public.excel.charting,
AccessHelp said:
However, there is a twist. I want to make my chart title (and text box)
both static and dynamic, which means a portion of my chart title is static
and the other portion is dynamic (e.g. Student Name: John Dole). "Student
Name" would be static, and John Dole would be dynamic from Cell A1 of Data
Sheet.

When I tried to input the formulas =concatenate("Student Name: ", 'Data
Sheet'!A1), I got an error "That Function is not valid.".

Lateral Thinking time: if formulas aren't possible in the Chart box
(they aren''t, only a simple cell reference) then have the chart box
refer to a cell which is itself a formula.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.