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.
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.
Rory Archibald
You can just add the text to the cell you are linking to?
ASKER
Tried that...get an invalid reference, using ampersand, a plus, quotation marks, etc.
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.
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
=<nameofworksheet>!<cell>
The formula that I used looks like this:
=Jan!B39
ASKER
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
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
ASKER
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?
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?
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
="Variance = "&text(G84,"$#,##0")
and then link the textbox to H84.
Regards,
Rory
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That did it! Thanks!
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. :)
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. :)
ASKER
Thanks for the patience!