Linking to custom number formats

[crew11k]

The issue: I want to display a cell with the value -1,000 as Negative 1,000. In custom number formats I have selected #;"negative" #,###;; which does indeed display the cell with -1000 as Negative 1,000, however, I want to be able to link to this cell and have the link pull in Negative $1,000. Is this possible?

Thanks so much

 

[Jerry Sullivan]

Welcome to the Board,

It's not clear to me what you mean by "have the link pull in Negative $1,000"

The easiest solution would be to format the cell that is referencing the other cell to have the format that you want....
#;"Negative" $* #,###;;

Would that work, or are you looking for something else?

 

[crew11k]

For example, cell A5 contains the value -1000 which displays as "negative 1,000" by using the custom number format #;"negative" #,###;.

Cell A6 contains a sentence where I want to link in the "negative 1,000" using concatenate. For example ="The value is negative 1,000." The formula I have used is ="The value is "&A5&"."

Copying the custom number format from A5 to A6 will not work it seems because of the concatenante formula.

I guess my question is, what is the best way to have the text "negative 1,000" be able to be linked to as a input into another cell using the concatenante.

Thanks so much!

 

[Matt Rogers]

Hi.

Maybe this?

Excel Workbook
	A
1	1000
2	-1000
3	$ The value is negative.
4	-1000 $ The value is negative.
Sheet

 

[VoG]

I think you'd need VBA for this. Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A5" Then
    Application.EnableEvents = False
    Target.Offset(1).Value = "Value is " & Target.Text
    Application.EnableEvents = True
End If
End Sub

then try changing the value in A5.

 

[Scott Huish]

Using the number format:
$#,##0;"Negative "$#,##0;$0

Incorporate this into your concatenation formula with the TEXT function:
="The Value is " & TEXT(A5,"$#,##0;""Negative ""$#,##0;$0")

Excel Workbook
	A	B	C
4
5	Negative $1,000		The Value is Negative $1,000
6
Sheet1

 

[crew11k]

Wow, thank you all for your quick responses. A special thanks to HOTPEPPER who gave the best answer which solved my issue. This was a very quick and easy application of cell referencing with custom number formats. Thank you.

Cheers

 

[crew11k]

One more follow-up issue related to custom number formatts.

In Excel 2007, cell A1 has the value 5.0%.

In A2 is the formula:
=TEXT(FIXED($A$1*100,1),"#,###.#;""negative ""#,###.#;#")

The displayed value in A2 is 5.

I want A2 to display 5.0, but for some reason when there is a 0 in the tenths decimile place, it does not show up. I tried the same formula with 5.5% in A1 and the formula properly displays 5.5

Does any one have an idea why when there is a .0 the formula chops off the 0?

Thanks!

 

[shg]

=TEXT(FIXED($A$1*100,1),"#,###.0;""negative ""#,###.0;#")

 

[Scott Huish]

I don't think you should need FIXED, and unless your values are going to be greater than or equal to 1000%, you should need all the #, part either:

=TEXT($A$1*100,".0;""negative "".0;0")