+ Reply to Thread
Results 1 to 59 of 59

Chart title referenced from a cell

  1. #1
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Chart title referenced from a cell

    Hello,

    I would like my chart title to automatically be the value of what is in cell A1.
    ive tried
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    but that doesnt work.

    is this even possible?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Chart title referenced from a cell

    you have to enter that in the formula bar. 1st, click on the "chart title" on the chart, then type on (or copy) the reference into the formula bar
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    thanks.

    if i want a word after the cell's value, how would i put that in the formula?
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    isnt working for me
    Last edited by tlacloche; 03-26-2013 at 07:10 PM.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Chart title referenced from a cell

    You need to add the text in the cell being linked to.
    The link itself can only contain a reference to cell(s)
    Cheers
    Andy
    www.andypope.info

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Chart title referenced from a cell

    Double Post!, so much for new server

    You need to add the text in the cell being linked to.
    The link itself can only contain a reference to cell(s)

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Chart title referenced from a cell

    you can also use a named formula but you have to set the formula up to point to a cell first and then amend it after linking the chart title to it
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  7. #7
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    can you elaborate a little more please JosephP. i dont quite understand.

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Chart title referenced from a cell

    sure thing
    1. create a named range that refers to the cell you want to use-say you call it MyChartTitle and refer to =Sheet1!$A$1
    2. link the chart title to the name using =Sheetname!MyChartTitle
    3. edit the named range to refer to =Sheet1!$A$1&" more text"

  9. #9
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    thats all well and good JosephP, but I have many charts which i want to link to cell A1. however each chart has a different text after the A1 value.

    for example, my first chart will have A1 value followed by "Profits". My next chart will have A1 value followed by "Sales" and so on.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Chart title referenced from a cell

    Then I would suggest that you create a table containing A1 and then all the other text you want following A1 - then use those new references in your chart

  11. #11
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    that defeats the purpose. I may as well manually title the charts.

    my plan is to make many duplications of this sheet so all i need to do is change A1 on each sheet and it will give me my desired chart title.
    hope that makes sense.

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Chart title referenced from a cell

    ok good luck

  13. #13
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    Don't you know how to do it then?

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Chart title referenced from a cell

    I offered a suggestion which you didnt like, maybe some-one else can offer a better 1

  15. #15
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    its not that i didnt like it. it just doesnt work for me.
    dont take it personally. i appreciate your effort

  16. #16
    Forum Contributor
    Join Date
    01-02-2007
    Location
    Australia NSW
    MS-Off Ver
    2013
    Posts
    494

    Re: Chart title referenced from a cell

    Can you not use ActiveSheet.Range("A1").text and use the worksheet activate event to add the chart title ?

  17. #17
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    i dont understand Coreytroy.
    Could you elaborate further please...

  18. #18
    Forum Contributor
    Join Date
    01-02-2007
    Location
    Australia NSW
    MS-Off Ver
    2013
    Posts
    494

    Re: Chart title referenced from a cell

    Right click on the tab and select View Code
    At the top right select worksheet Activate event

    Add the following before End Sub

    Please Login or Register  to view this content.
    Change Chart 1 to chart name to suit.

  19. #19
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    doesnt work for me. i get a macro error that gives me the options of debug and end.

    anyone else....

  20. #20
    Registered User
    Join Date
    12-03-2012
    Location
    Canada
    MS-Off Ver
    Access 2007 Excel 2007
    Posts
    48

    Re: Chart title referenced from a cell

    Please Login or Register  to view this content.
    This will change the title's text to a value in cell A1.
    Last edited by dagagnon; 04-03-2013 at 01:36 PM.

  21. #21
    Registered User
    Join Date
    12-03-2012
    Location
    Canada
    MS-Off Ver
    Access 2007 Excel 2007
    Posts
    48

    Re: Chart title referenced from a cell

    Quote Originally Posted by coreytroy View Post
    Right click on the tab and select View Code
    At the top right select worksheet Activate event

    Add the following before End Sub

    Please Login or Register  to view this content.
    Change Chart 1 to chart name to suit.
    Not .Text but with .Value .

  22. #22
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    will this allow me to title my charts with the A1 value followed by a text of my choice (e.g. Graph).
    so when the sheet is duplicated the first word of the chart title will be the A1 value, and the second word is Graph

  23. #23
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Chart title referenced from a cell

    the named range method would-you simply create a different name to use with each chart title
    you could also use two textboxes-one linked to A1 and the other containing your static text for each chart. if the charts are embedded on the sheet you could also use cells above the charts to contain the titles

  24. #24
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    i have the following macro on the sheet.

    Please Login or Register  to view this content.
    i dont know where to add the chart title macro.

  25. #25
    Forum Contributor
    Join Date
    01-02-2007
    Location
    Australia NSW
    MS-Off Ver
    2013
    Posts
    494

    Re: Chart title referenced from a cell

    Please Login or Register  to view this content.
    Last edited by coreytroy; 04-06-2013 at 07:41 PM.

  26. #26
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    thanks coreytroy.
    that puts the A1 cell value in the chart title, but where in the macro do i insert my text of choice? or do i do that manually on the chart title?

  27. #27
    Forum Contributor
    Join Date
    01-02-2007
    Location
    Australia NSW
    MS-Off Ver
    2013
    Posts
    494

    Re: Chart title referenced from a cell

    I think you may have to use an Inputbox for the added text.

    Please Login or Register  to view this content.
    Untested code.

  28. #28
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    we're getting there...

    however, every time i visit the sheet and the macro is applied, a small box appears asking me to enter the required text. it doesn't automatically add the text from the macro into the chart title.

    any ideas why?
    Last edited by tlacloche; 04-10-2013 at 08:58 AM.

  29. #29
    Forum Contributor
    Join Date
    01-02-2007
    Location
    Australia NSW
    MS-Off Ver
    2013
    Posts
    494

    Re: Chart title referenced from a cell

    The code should set the chart title as what is in cell A1, and what text was entered into the inputbox.

    Isn't that what you were after?


    See example file.
    Attached Files Attached Files

  30. #30
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    maybe you're not getting the same thing i am...

    if i visit "sheet 2", then go back to "sheet 1", the small box appears asking me to enter the text i want in the chart title. this happens each time i visit "sheet 1", even after I have already filled in the box the last time i visited the sheet.
    i want the sheet to already know what text i want directly from the macro. i dont want to keep inputting the text in the small box every time i click on "sheet 1"
    Last edited by tlacloche; 04-10-2013 at 07:33 PM.

  31. #31
    Forum Contributor
    Join Date
    01-02-2007
    Location
    Australia NSW
    MS-Off Ver
    2013
    Posts
    494

    Re: Chart title referenced from a cell

    So the actual text added to the cell value will not change, and is fixed ?

  32. #32
    Forum Contributor
    Join Date
    01-02-2007
    Location
    Australia NSW
    MS-Off Ver
    2013
    Posts
    494

    Re: Chart title referenced from a cell

    If the added text will ALWAYS be fixed, then try the below, by changing the 'Additional text - fixed' with what you want to add to the cell value.
    Untested.
    Please Login or Register  to view this content.

  33. #33
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    doesnt work.
    getting a "Compile error. Syntax error."

    For some reason the debugger highlights the first line in yellow - "Private Sub Worksheet Activate ( )"

  34. #34
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Chart title referenced from a cell

    that's not what the first line said-did you copy and paste it?

  35. #35
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    it is the first line. look at the code Coreytroy posted above my post. "Private Sub Worksheet Activate ( )" is the first line.

  36. #36
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Chart title referenced from a cell

    no it isn't-read it carefully ;-)

  37. #37
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    i think i know where the confusion is.
    i already had the following macro
    Please Login or Register  to view this content.
    coreytroy was giving me his chart title macro combined with the one i already have.
    "Private Sub Worksheet Activate ( )" has always been the first line of my macro and will continue to be.

  38. #38
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Chart title referenced from a cell

    nope-there's an underscore between Worksheet and Activate otherwise the syntax would be invalid and it wouldn't compile

  39. #39
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    yes ive included that. i did copy and paste the code

  40. #40
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Chart title referenced from a cell

    For some reason the debugger highlights the first line in yellow - "Private Sub Worksheet Activate ( )"
    no underscore there and that would produce the syntax error you described

  41. #41
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    there is an underscore there. please see the attached file which shows a screenshot.
    after i click OK on the syntax error box this is what it looks like:
    Attached Images Attached Images

  42. #42
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Chart title referenced from a cell

    then it would have helped if you posted the actual code that is present

    the error is in the line highlighted in red
    Please Login or Register  to view this content.
    should be one word

  43. #43
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    changed that but now get a box with "Compile error: Else without if".
    The word "Else" is highlighted.

    Here's the code:

    Please Login or Register  to view this content.

  44. #44
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Chart title referenced from a cell

    Please Login or Register  to view this content.
    needs to be two lines if you have an else section
    Please Login or Register  to view this content.

  45. #45
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    made the changes and there are no errors HOWEVER the word "profits" is not on the chart title

    here's the code:

    Please Login or Register  to view this content.

  46. #46
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Chart title referenced from a cell

    there's nothing in that code to add the word 'profits'-it only checks if it's there already. I reckon you want
    Please Login or Register  to view this content.

  47. #47
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    wow. you got it. thank you so much.

    just out of curiosity, if i wanted to also do the same with chart 2 and chart 3 but instead of the word "profits" i want "sales" and "cost" how would i add it to your macro?

  48. #48
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Chart title referenced from a cell

    one way would be like this
    Please Login or Register  to view this content.

  49. #49
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    i probably should have mentioned that the chart names are not simply 1, 2, 3.....
    its more like chart 1, chart 5, chart 11, chart 13.....

    can your macro work with that?

  50. #50
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Chart title referenced from a cell

    not without some way of identifying which chart gets which title

    assuming you know the names in advance, perhaps
    Please Login or Register  to view this content.
    Last edited by JosephP; 04-11-2013 at 11:06 AM. Reason: tweak

  51. #51
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    is it not possible to duplicate the following and replace "chart 1" with the correct chart number:

    Please Login or Register  to view this content.
    im afraid my excel knowledge is a fraction of yours. i appreciate your patience and time mate

  52. #52
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Chart title referenced from a cell

    see the amendment I made to the last post-you just need to make sure the elements in each array are in the same order

  53. #53
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    i input the following but only the first 3 charts are changing:

    Please Login or Register  to view this content.

  54. #54
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Chart title referenced from a cell

    sorry-I actually changed the code again in case you had more than 3 charts but I think you got the code before I changed it

  55. #55
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    YES!!!!
    thank you!!!!

    one last last thing.
    on my other sheets i have used the following code to protect the sheet (as well as the autofilter:

    Please Login or Register  to view this content.
    however, on your code youve used the following lines of code:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    whats the difference between the two?

  56. #56
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Chart title referenced from a cell

    no functional difference but if you change the password you only have to do it in one place using my code ;-)

  57. #57
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Chart title referenced from a cell

    got it.
    thanks again

  58. #58
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Chart title referenced from a cell

    you're welcome

  59. #59
    Forum Contributor
    Join Date
    01-02-2007
    Location
    Australia NSW
    MS-Off Ver
    2013
    Posts
    494

    Re: Chart title referenced from a cell

    Took a while, but a win none the less.
    Last edited by coreytroy; 04-11-2013 at 04:36 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1