Excel - reference another cell in a graph title? 1

[baddy]

I have a chart that changes when the user puts in different values for HLOOKUP, and I would like the chart title to reflect that. What I would like to do is have it refence a cell, so it would be something like "Statistics for A1", where instead A1 would actually be the contents of the A1 cell (if A1 was "oranges", the title would say "Statistics for oranges&quot. Is this possible?

 

[SkipVought]

Hi,

Point your Chart Title to a FIXED CELL.

In the FIXED CELL, insert a formula to reflect the selection.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[baddy]

Skip, how do I point the title to a fixed cell? Not having much luck figuring out how to do that. Thanks a lot.

 

[SkipVought]

Hey baddy,

I thought I had done this before with a title. I was wrong.

Tell me if this is a sheet embedded chart or a Chart Sheet. Maybe we can get you some code that will do what you want to do.

Sorry

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[baddy]

No problem Skip. This is just an embedded chart in a spreadsheet. Thanks for taking the time to try and help me figure this out. When trying to make the code a formula, it reject everything with an error (even just '=A1'); and doing A1 or $A$1 just displayed them as straight text.

Patrick

 

[xlbo]

Can be done on either
Click once in the title
Click again to get the cursor to show in the title
Click in the formula bar
enter =
use the mouse to select a cell that you want to reference or if you know the cell reference, enter it manually
press return
et voila

Took me a little while to remember as well - I generally set up my charts thru code and enter the title there rather than manually

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[SkipVought]

Here's what to do...

1. Name the cell that has the formula reflecting the title, MyTitle

2. in the Sheet Object with the source data (right click on sheet tab and select View Code) paste this code...

Private Sub Worksheet_Change(ByVal Target As Range)
    ChangeChartTitle
End Sub

3. Insert a new module and paste this code...

Sub ChangeChartTitle()
    On Error GoTo ChartIt
    ActiveSheet.ChartObjects(1).Chart.ChartTitle.Caption = [MyTitle]
    Exit Sub
ChartIt:
    Charts(1).ChartTitle.Caption = [MyTitle]
End Sub

Now whenever you make a change on the data sheet, your chart title will reflect that data in [MyTitle]

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Geoff,

I know that I had done this before, but I use Named Ranges almost exclusively and guess what -- I could NOT get a named range to work.

Cell reference, YES, Named Range, NO!

Go figger!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

Yup - one of them weird little excel weirdnesses ;-)

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244