Dynamic Chart Titles w/o VBA

    I've never seen this on a tip sheet before, and I don't know where I learned it myself. It may be one of those (rare) things I learned all on my own.


    If you want to create a title for a chart that updates with your data, it's pretty simple.


    Create a formula (one line only, but you can use CHAR(10) to create multi-line titles) that refers to the information you want in the chart title.


    I often do this on a hidden sheet, so that users don't inadvertently mess it up. Something like:


    Let's say that this is on sheet "Hide Me", cell A1:
    =Sheet1!A1 & " as of " & CHAR(10) & TEXT( NOW(), "d-Mmm-yyyy")


    In the chart, select the existing title and IN THE FORMULA BAR enter:
    = 'Hide Me'!A1 (You can't enter this formula in the 'Chart Title' block in the dialog box, or it will come out as plain text. But do enter 'something' there, so that you have a title to 'edit' in the formula bar.)


    Your chart will print with a new date each day, and you (or your user) won't have to remember to update the title.

    Re: Dynamic Chart Titles w/o VBA


    I didn't know there were people out there who actually used VBA for this?


    But you're right, it's a good tip for newbs to latch onto. Works for more than just chart titles though... Applies to any control that accepts text that you'd want to put on a chart. For that matter, it doesn't have to be a control on a chart either. Could be any control that accepts text or a caption anywhere in your workbook. Maybe you want a big heart autoshape on a sheet that refers to a cell... same logic applies.


    You can throw as many textboxes on your chart as you want and have them reference cells all over the place.


    And yes, if you don't know about it, it's very handy indeed.


    BTW, you might also be interested to know that you don't even need to store that formula in a cell. You can assign it to a defined workbook name and just refer to that!

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login