Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting a Cell Reference with a Formula
I have tried to do this and cannot, but my formula is a concatenation of up
to 7 fields, including both numbers and text. Although I've formatted the cells that the concatenated formula is pulling, the dollar amounts are not being formatted within the concatenated formula result. Is there a way to format dollars within a concatenation formula? My formula reads as: =IF(ISBLANK($H9),"",CONCATENATE($H9," - ","(",I9,")"," ",$J9," ","$",$K9," ",$L9," "," ","(",$M9,")")) I would like the dollar field K9 to show as dollars with dollar sign and appriopriate commas. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting a Cell Reference with a Formula
Replace you reference $K9 with:
TEXT($K9,"$#,##0.00") and remove the "$" before it. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "CC" wrote in message ... I have tried to do this and cannot, but my formula is a concatenation of up to 7 fields, including both numbers and text. Although I've formatted the cells that the concatenated formula is pulling, the dollar amounts are not being formatted within the concatenated formula result. Is there a way to format dollars within a concatenation formula? My formula reads as: =IF(ISBLANK($H9),"",CONCATENATE($H9," - ","(",I9,")"," ",$J9," ","$",$K9," ",$L9," "," ","(",$M9,")")) I would like the dollar field K9 to show as dollars with dollar sign and appriopriate commas. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting a Cell Reference with a Formula
In your formula, replace the reference $K9 by TEXT($K9,"$0.00") or whatever
variation you like on the format. [Look at the format of your source cells if you want to get a clue what format string to use.] -- David Biddulph "CC" wrote in message ... I have tried to do this and cannot, but my formula is a concatenation of up to 7 fields, including both numbers and text. Although I've formatted the cells that the concatenated formula is pulling, the dollar amounts are not being formatted within the concatenated formula result. Is there a way to format dollars within a concatenation formula? My formula reads as: =IF(ISBLANK($H9),"",CONCATENATE($H9," - ","(",I9,")"," ",$J9," ","$",$K9," ",$L9," "," ","(",$M9,")")) I would like the dollar field K9 to show as dollars with dollar sign and appriopriate commas. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting a Cell Reference with a Formula
THANK YOU SOOOO MUCH!
"Sandy Mann" wrote: Replace you reference $K9 with: TEXT($K9,"$#,##0.00") and remove the "$" before it. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "CC" wrote in message ... I have tried to do this and cannot, but my formula is a concatenation of up to 7 fields, including both numbers and text. Although I've formatted the cells that the concatenated formula is pulling, the dollar amounts are not being formatted within the concatenated formula result. Is there a way to format dollars within a concatenation formula? My formula reads as: =IF(ISBLANK($H9),"",CONCATENATE($H9," - ","(",I9,")"," ",$J9," ","$",$K9," ",$L9," "," ","(",$M9,")")) I would like the dollar field K9 to show as dollars with dollar sign and appriopriate commas. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting a Cell Reference with a Formula
No, I'm afraid that formulas cannot format cells.
-- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "CC" wrote in message ... Is there a way to make parts of a formula bold and or italic? "Sandy Mann" wrote: Replace you reference $K9 with: TEXT($K9,"$#,##0.00") and remove the "$" before it. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "CC" wrote in message ... I have tried to do this and cannot, but my formula is a concatenation of up to 7 fields, including both numbers and text. Although I've formatted the cells that the concatenated formula is pulling, the dollar amounts are not being formatted within the concatenated formula result. Is there a way to format dollars within a concatenation formula? My formula reads as: =IF(ISBLANK($H9),"",CONCATENATE($H9," - ","(",I9,")"," ",$J9," ","$",$K9," ",$L9," "," ","(",$M9,")")) I would like the dollar field K9 to show as dollars with dollar sign and appriopriate commas. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting a Cell Reference with a Formula
To expand on Sandy's answer, a formula in conditional formatting can format
a cell, but not part of a cell. A cell which contains text can have a format applied to part of the text, but that can't be done if the text contains a formula, rather than just text. -- David Biddulph "Sandy Mann" wrote in message ... No, I'm afraid that formulas cannot format cells. "CC" wrote in message ... Is there a way to make parts of a formula bold and or italic? "Sandy Mann" wrote: Replace you reference $K9 with: TEXT($K9,"$#,##0.00") and remove the "$" before it. "CC" wrote in message ... I have tried to do this and cannot, but my formula is a concatenation of up to 7 fields, including both numbers and text. Although I've formatted the cells that the concatenated formula is pulling, the dollar amounts are not being formatted within the concatenated formula result. Is there a way to format dollars within a concatenation formula? My formula reads as: =IF(ISBLANK($H9),"",CONCATENATE($H9," - ","(",I9,")"," ",$J9," ","$",$K9," ",$L9," "," ","(",$M9,")")) I would like the dollar field K9 to show as dollars with dollar sign and appriopriate commas. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formatting a cell to display a word when i enter a reference lette | Excel Discussion (Misc queries) | |||
Formatting the value of a cell reference in a formula | Excel Worksheet Functions | |||
cell reference changes and conditional formatting | Excel Worksheet Functions | |||
Conditional formatting a full row triggered by a single cell reference... | Excel Worksheet Functions | |||
How do I copy formatting in a cell reference | Excel Discussion (Misc queries) |