Excel 2007 : Formatting Cells as Curency with "&" formula appending text

[dancor31]

I am having trouble trying to figure out how to format cells that are suppose to contain currency values that also have text appended to the value. I have a situation where if the value of "123" were to be entered into cell H1 then a sum value appears in cell U4. U4 represents a sum of “Hardware Values” so I have used the formula =IF(H1=123,U4&" Hardware Values", ""). This works fine with the one exception that the end result loses its currency format.

Example: the expected result of the cell displaying $1,000.00 Total Hardware end s up looking like 1000 Total Hardware. Is there a way to correct this?

Likewise, I have a similar formatting situation where rather than currency not displaying as such, number values extend beyond what I have valued for decimals. Example 12.05 Units displays as 12.055555555 Units. I would like to limit the value to the following format: “0.00 Units”

Can anyone help me out?

[dip11]

I don't think the former is possible.

For the latter, attach a spreadsheet where you have that issue. Ensure though that you mean the cell actually displays 12.055555555 and its not just when you double click or view in the formula bar that it displays as 12.055555555 because that is normal.

[snb]

=If (H1=123,TEXT(U4;"$ 0") & " Hardware Values","")  

replace $ by the currency character you need.

[jpr73]

Hi,
Try this :

=If (H1=123,U4,"")  

and this personalized format :

"$ "## ##0,00" Hardware Values"  

The advantage compared to the solution of SNB is that the cell can be reused for a new calculation
Best regards

[jpr73]

Hi,
Sorry the personalized format for US citizen is not the comma but the point:

"$ "## ##0.00" Hardware Values"  

[dancor31]

I apologize, I'm not familiar with the "personalized format" piece of this... Where would I enter that information?

[dancor31]

Hey, thanks for the help. It realized what you refering to and did a quick fix. thanks again for your help