Calculate Chart Title 2

[Quintios]

I would like to be able to dynamically change the title of a chart depending on the contents of two cells. For example, in cell one I have "1/1/2003", and in cell two I have "2/1/2003". The chart should read:

1/1/2003 to 2/1/2003 Data

If someone changes the contents of the cells, then the chart title needs to update as well to reflect the different data.

Is there a way to do this?

Thanks in advance! Onwards,

Q-

 

[MMSantiago]

I created the following macro and then just ran it whenever I needed the header changed:

Sub Change_Header()
ActiveSheet.PageSetup.LeftHeader = Format([A1], "MMM DD, YYYY&quot
ActiveSheet.PageSetup.CenterHeader = Format([A2], "MMM DD, YYYY&quot
End Sub


With this, cell A1 will be the first date and cell A2 is your second date. You'll have to experiment to get the text in between the dates. You can also check out Microsoft Knowledge Base Article Q213742, which is where I got the idea. Hope this helps.

 

[dianemarie]

Sure, set up a formula in a cell on the source data page that concatenates the two years, and you can add whatever text. Example if Year 2002 is cell a1 and 2003 is a2:

=concatenate(a1," thru ",a2," data&quot You can concatenate with plus signs also, see Help if you're not familiar with it.

Then in the chart, click the chart title so it is selected (if there's text in the chart title already it doesn't matter, it will get overwritten). Type an equal sign in the formula bar (not the title box) and key in the cell address that has the formula.

 

[bandit600]

dianemarie,

This works well and will be useful for me, so have a Star for that.

The only thing I would emphasise for those unfamiliar with Concatenate, is that the cells where the dates are entered/amended must be formatted as Text rather than Dates.

bandit600

 

[Quintios]

Sorry I didn't give you a star earlier...

I didn't use your method *exactly* as it wasn't appropriate for the particular chart. I left the title alone and instead put a text box below the title for a "sub title" that indicated a date range that the user specified.

Either way, it worked! Thank you! Onwards,

Q-