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

• Jun 17, 2003

1
• #2

EGT01

Chemical

Dec 11, 2002

435

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.

 

Upvote 0 Downvote

• Jun 17, 2003

1
• Thread starter
• #3

GBinns

Chemical

Mar 19, 2003

11

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.

 

Upvote 0 Downvote

• Jun 17, 2003

1
• #4

lilliput1

Mechanical

May 1, 2003

1,054

Yes you can create a formula with text & cell references. Enclose text within " 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:

="Pressure Drop for "&C2&" Inch Pipe"

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

="Pressure Drop for "&C2&"""&" Inch Pipe"

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

 

Upvote 0 Downvote

• Jun 17, 2003

• Thread starter
• #5

GBinns

Chemical

Mar 19, 2003

11

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 ="Pressure Drop for "&C2&" Inch Pipe" in the title text box.

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

 

Upvote 0 Downvote

• Jun 17, 2003

5
• #6

pmover

Mechanical

Sep 7, 2001

1,507

the answer to your question is "yes", a chart title can be linked to a value or text within a cell.
1) simply create the chart and insert a "dummy" 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 "=" (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

 

Upvote 0 Downvote

• Jun 18, 2003

• #7

yakpol

Structural

Jun 1, 2001

450

pmover,
I never knew that, thanks a lot!

 

Upvote 0 Downvote

• Jun 18, 2003

• #8

pmover

Mechanical

Sep 7, 2001

1,507

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

 

Upvote 0 Downvote

• Jun 19, 2003

• #9

joerd

Chemical

Sep 27, 2002

531

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.

 

Upvote 0 Downvote

• Sep 15, 2003

• #10

Netdream

Industrial

Sep 15, 2003

3

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

 

Upvote 0 Downvote

• Sep 15, 2003

• #11

pmover

Mechanical

Sep 7, 2001

1,507

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.

="minimum dollar ="&" $"&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

 

Upvote 0 Downvote

• Sep 15, 2003

• #12

Netdream

Industrial

Sep 15, 2003

3

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 "June 30, 1998", and it is not working: Any idea why?

 

Upvote 0 Downvote

• Sep 16, 2003

• #13

pmover

Mechanical

Sep 7, 2001

1,507

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: ="minimum dollar ="&" $"&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: ="date is:"&FIXED(D9,2)
result is: date is: 35,976.00

well, u need to convert the date to text.

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

good luck!
-pmover

 

Upvote 0 Downvote

• Sep 17, 2003

• #14

Netdream

Industrial

Sep 15, 2003

3

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?

 

Upvote 0 Downvote

• Sep 17, 2003

• #15

pmover

Mechanical

Sep 7, 2001

1,507

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

 

Upvote 0 Downvote

• Sep 23, 2003

• #16

joerd

Chemical

Sep 27, 2002

531

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: ="minimum dollar ="&DOLLAR(D4,2)
result is: minimum dollar = $827,000.00

or
cell value is: 827000
formula is: ="minimum dollar ="&TEXT(D4,"$#,##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.

 

Upvote 0 Downvote