(Note: Video version above, text version below)
How do you create a dynamic text string in Excel with values that are formatted properly? Let’s see how:
In this worksheet, I have the following formula in cell B4:
Basically it multiplies the percentage in cell D4 times the Account Value in cell B2, but if that amount exceeds the Cap in cell C4, it then just inserts the Cap:
(note: click on the image for a better view)
Then in cell E4 I have a note explaining the formula: 10% of account with a maximum of $300,000.00.
This all works fine, except if I want to change the Cap or Percentage, then I need to manually edit the note. How can I set up the note to automatically change when I modify either of these values? That’s easy! I will use concatenation with the & symbol. The formula now in cell E4 will read:
=D4& ” of account with a maximum of “&C4
The only problem is that the format of the 10% and the $300,000.00 does not carry into the dynamic note. To accomplish this, I need to include the TEXT function and the formatting for each of those cells. The syntax for the TEXT function is =TEXT(value,format_text). The format for the percentage I want is “0%”. The format for the dollar amount is “$#,$$0.00″. Note that both need to be in double quotes.
Now my formula will read:
=TEXT(D4,”0%”)& ” of account with a maximum of “&TEXT(C4,”$#,##0.00”)
This results in the note to read properly:
Now, when I change the Percentage or the Cap, the note automatically changes and the formatting is correct!