[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • Last Modified:

How can I add a descriptor/title to a text box that is linked to a formula?

Hi,
I could not remember how to link a text box to a cell (formula) but I did find a solution on this site.

However, I would like to add a descriptor (title) to that formula linked text bix so people know what that # is.  Do any of you have an idea on how to do that?  Would it be just adding a 2nd text box,or can I actually put a descriptor / title in the formula linked text boxed?

Thanks.
0
amkazen
Asked:
amkazen
  • 5
  • 4
  • 2
1 Solution
 
Rory ArchibaldCommented:
You can just add the text to the cell you are linking to?
0
 
amkazenAuthor Commented:
Tried that...get an invalid reference, using ampersand, a plus, quotation marks, etc.
0
 
Rory ArchibaldCommented:
You need to put the text into the cell, then your formula link from the text box is just a straight link to the cell. You cannot put a formula (other than a direct cell link) into the textbox.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
drozeveldCommented:
If I am understanding you correctly, Insert your text box inside the graph area, click on the outer edge of the text box to change the look from ///// to more of a solid line, then click up in the formula bar and type:

     =<nameofworksheet>!<cell>

The formula that I used looks like this:
 
     =Jan!B39
0
 
amkazenAuthor Commented:
Guess I am being dense...
cell g84 displays $683,005 as a result of the formula in that cell:  G82-G83
my text box is linked to cell g84:  =$G$84 and the text box displays $683,005
I would like to have the text box display  Variance = $683,005
The problem is when I type in the text box:
="Variance ="$G$84 I get "The text you entered is not a valid reference or defined name", or
"Variance ="$G$84 I get "The reference is not valid", or
="variance ="&$G$84 I get "The text you entered is not a valid reference or defined name", or
"variance ="&$G$84 I get "The reference is not valid", or
"variance ="!$G$84  I get "The reference is not valid", or
variance =!$G$84  I get "The reference is not valid", or
if I type  Variance = into it's own cell H84 and then type in the text box
=$H$84 I get Variance = in the text box but then when I try to combine H84 with G84 using ampersands or pluses or exclamation points I get the same two messages above, depending on what I have tried
=$h$84&$g$84 or =$h$84+$g$84 or = $h$84!$g$84

0
 
amkazenAuthor Commented:
Ok, I combined H84 & G84 into a 3rd cell, i84, but then I lose the # formatting and get  
 variance = 683005.08
but I would like to be able to see   variance = $683,005

I also tried resizing the text box because some of the #'s are negative and instead of getting Variance = ($124,375) I get  Variance = -$124375.34  

Is there a way to put a return inside the text box?
0
 
Rory ArchibaldCommented:
As I said, you have to add the text to the cell you are linking to - you CANNOT do the linking in the textbox formula. In H84 put:
="Variance = "&text(G84,"$#,##0")
and then link the textbox to H84.
Regards,
Rory
0
 
Rory ArchibaldCommented:
To cater for negatives use:
="Variance = "&TEXT(G84,"$#,##0;($#,##0)")
0
 
amkazenAuthor Commented:
That did it!  Thanks!
0
 
drozeveldCommented:
This is what I would maybe do:

In a cell somewhere on the worksheet, enter the word:  Variance
In the cell right next to it, enter your formula"  =$G$84

Format the text in both of these cells at "white" so that they do not show when you print the page, or put them outside of your print area.

In the next cell to the right enter this formula:  =CONCATENATE(A3," ",B3) and also format this to white.

Then in the text box, do as I suggested as above for the text box to equal what you entered into the 3rd cell.

Hope this works.  It may not be the most condensed way to do it, but I have found sometimes you have to be crafty to get it to work.  :)
0
 
amkazenAuthor Commented:
Thanks for the patience!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now