How Do I Link a Chart Title to a Cell? 8

[GBinns]

I would like to link an Excel chart title to a cell in a worksheet. Does anybody know how to do this?

In other words, I have a spreadsheet cell that changes based on inputs from the user. I want the title of my chart to change with that cell.

E.g.: My cell in question reads, <4&quot;> and I want my chart title to read, <Pressure Drop for 4&quot; Pipe>. And I want the title to change to 6&quot; pipe if that's what the user inputs. I know about the concatenate command, but I don't know how to use it in a chart title.

Can anyone help me out, please?

 

[EGT01]

I think all of the titles for a chart are like text boxes. All you need to do is click the text box, then place your mouse pointer in the formula bar area, hit the equal sign and then place your mouse pointer in the cell you want to link to, then hit enter.

 

[GBinns]

Thanks, that's exactly what I was after.

Too bad, though, I can't use a fomula inside a text box. I have to have the formula inside the worksheet cell, and then refer to that cell in the text box.

 

[lilliput1]

Yes you can create a formula with text & cell references. Enclose text within &quot; and use & to connect to cell reference. for example if the pipe size is user input at cell C2 and say your title is at cell A5 in cell A5 wite the formula:

=&quot;Pressure Drop for &quot;&C2&&quot; Inch Pipe&quot;

I had to write out inch but see if the formula below will work

=&quot;Pressure Drop for &quot;&C2&&quot;&quot;&quot;&&quot; Inch Pipe&quot;

note blank within the quote marks will show up as a space

 

[GBinns]

Yeah, but I still have to assemble the title in cell A5 and then reference cell A5 in the title text box. I can't just enter the formula =&quot;Pressure Drop for &quot;&C2&&quot; Inch Pipe&quot; in the title text box.

At least my computer's not letting me do it.

 

[pmover]

the answer to your question is &quot;yes&quot;, a chart title can be linked to a value or text within a cell.
1) simply create the chart and insert a &quot;dummy&quot; chart title.
2) select the chart title (not the text within the title) - do NOT have the blinking cursor within the textbox, but the textbox itself.
3) type &quot;=&quot; (w/o quotes), like entering a formula, and then select the cell with information to be displayed.

when the cell value is changed, so is the chart title.

this procedure does apply to other textboxes on charts, including datalabels.

i frequently accomplish this task, so no big deal.
please advise status!
-pmover

 

[yakpol]

pmover,
I never knew that, thanks a lot!

 

[pmover]

yakpol,
glad to have provided a tip!
i've been using that feature for quite awhile - especially for monitoring equipment performance (i.e. equipment id # is chart title or some other textbox within chart).
-pmover

 

[joerd]

GBinns,

You are right, you can only reference a range (which may consist of multiple cells) in a text box. A formula with concatenations (&'s) just isn't supported. Not even when you make up a Name definition containing the formula and then reference the Name in the text box.
Too bad, should be nice in a future version of Excel...

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Netdream]

I have the same problem but when I reference a single cell that displays $876,000 in bold letters, what I get is,876000.

Is there any way to fix this problem?

Thanks

 

[pmover]

Netdream,

if you are attempting to get the dollar sign (or any other character), enter the following formula in a cell. then create the text on the chart and then create the link as previously posted.

=&quot;minimum dollar =&quot;&&quot; $&quot;&FIXED(C9,2)

in this example, cell c9 contains the minimum function. the formula uses xl's standard concatenate capabilities to combine text strings and numbers.
-pmover

 

[Netdream]

Thank you for the quick response.

For the dollar sign it may work, but this trick it will not work for the comma; $827,000

I am also trying to point to a cell that has a date &quot;June 30, 1998&quot;, and it is not working: Any idea why?

 

[pmover]

um,

the comma was inserted when i tried it. perhaps the version of excel, how you have excel setup, or how your pc regional settings are established is the reason why the comma does not show.

cell value is: 827000
formula is: =&quot;minimum dollar =&quot;&&quot; $&quot;&FIXED(D4,2)
result is: minimum dollar = $827,000.00

as far as the date is concerned, you probably get something like this:

cell value is: 30-Jun-98
formula is: =&quot;date is:&quot;&FIXED(D9,2)
result is: date is: 35,976.00

well, u need to convert the date to text.

formula is: =&quot;date is:&quot;&TEXT(D9,&quot;mmm dd, yyy&quot
result is: date is:Jun 30, 1998

good luck!
-pmover

 

[Netdream]

Thank you.

This is very helpful;

I really appreciate it!!!


George


P.S. One last thing; is there anyway that I can make only $827,000 appear in bold letters?

 

[pmover]

p.s. if no link in textbox, then individual text can be formatted to your desire.
if there is a link, then entire text message or link can be formatted to your desire - not text specific.
to change formats, make selection and right-click to get sub-menu, and select format text box.
-pmover

 

[joerd]

As pmover said, boldface is not an option; you'll have to split the text into 2 cells.
Instead of the FIXED function, you can also try:
cell value is: 827000
formula is: =&quot;minimum dollar =&quot;&DOLLAR(D4,2)
result is: minimum dollar = $827,000.00

or
cell value is: 827000
formula is: =&quot;minimum dollar =&quot;&TEXT(D4,&quot;$#,##0.00&quot
result is: minimum dollar = $827,000.00


Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.