Inserting a formula into a text string

[Courie]

Is there a way to insert a formula or reference a cell on another worksheet
in the middle of a text string.

For example if I wanted to say: XYZ company will pay $___________ of your
shipping costs... Where the blank was a reference from another sheet.

 

[David Biddulph]

="XYZ company will pay $"&Sheet6!A1&" of your shipping costs"

 

[MyVeryOwnSelf]

Is there a way to insert a formula or reference a cell on another

worksheet in the middle of a text string.

For example if I wanted to say: XYZ company will pay $___________ of
your shipping costs... Where the blank was a reference from another
sheet.

One way is to use a formula like this:
="XYZ company will pay "&TEXT(Sheet2!A1,"$0.00")&" of your shipping costs"

The ampersand does concatenation of strings.

 

[daddylonglegs]

Another way is to use

="XYZ company will pay "&DOLLAR(Sheet2!A1)&" of your shipping costs"

 

[Courie]

I have tried all of your suggestions--Thanks, by the way--but each of them
simply prints the verbiage of the function, not the result.

What could I be doing wrong?

 

[Pete_UK]

Maybe your cell containing the formula is formatted as Text. Use Format
| Cells | Number (tab) and choose General. Then double-click the cell
as if to edit it in order to activate the change in format.

Hope this helps.

Pete

 

[Courie]

Thanks for your suggestion Pete, unfortunately, that didn't work either. The
cells were already set to General and I set them to Text, then re-set to
General and recalculated the sheet, but there was no change. The forumla
still prints and views as text.