Prev: Excel 2007 Selection Tool Broken?
Next: attachment file limit
From: tran1728 on 8 May 2010 00:06
Dear All, pls help me.
when I type $ 52.00 on C1, I want C1 display positive $52 or negative ($ 52)
, if B1 is positive or negative, I want the entire column C.
Exemple :
C1 : I type $52, it display for me $52 , because B1 is positive
C2 : I type $52, it display for me ($52) , because B2 is negative
B C
B1 5 $52
B2 (3) ($52)

It is possible ?
Thank you for your help.

From: JLatham on 8 May 2010 01:31
This can only be done with VBA and the code below will do it for you. To use
the code, open the workbook and go to the sheet you want this to work on and
then:
Right-click on the worksheet's name tab and choose [View Code] from the list.
Copy the code below and paste it into the code module presented to you in
the previous step.
Close the VB Editor and test it by making entries into columns B and C.

Note that you must make the entry into column B before you make the entry
into C. As written, it does not change the entry in C if you make a change
in B after an entry is already in C. If you change B, then you would need to
re-enter into C to see the change made.

Private Sub Worksheet_Change(ByVal Target As Range)
'only operates when a single cell in column C changes
If Target.Column <> 3 Or _
Target.Cells.Count > 1 Then
Exit Sub ' not in column C
End If
'if both B# and C# have entries
'then make C entry same sign (+/-) as B
If Not IsEmpty(Target) And _
Not IsEmpty(Target.Offset(0, -1)) Then
If Sgn(Target.Offset(0, -1)) <> Sgn(Target) Then
Target = Target * -1
End If
End If
End Sub


"tran1728" wrote:

> Dear All, pls help me.
> when I type $ 52.00 on C1, I want C1 display positive $52 or negative ($ 52)
> , if B1 is positive or negative, I want the entire column C.
> Exemple :
> C1 : I type $52, it display for me $52 , because B1 is positive
> C2 : I type $52, it display for me ($52) , because B2 is negative
> B C
> B1 5 $52
> B2 (3) ($52)
>
> It is possible ?
> Thank you for your help.
>
From: Ms-Exl-Learner on 8 May 2010 01:57
Copy and paste the below formula in C1 cell.
=IF(B1>=0,52,IF(B1<0,-52,""))

Select the entire C column do right click>>Format
Cells>>Number>>Category>>Currency>>Symbol>> Select $>>Negative
Numbers:>>select ($1,234.10) and give ok.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"tran1728" wrote:

> Dear All, pls help me.
> when I type $ 52.00 on C1, I want C1 display positive $52 or negative ($ 52)
> , if B1 is positive or negative, I want the entire column C.
> Exemple :
> C1 : I type $52, it display for me $52 , because B1 is positive
> C2 : I type $52, it display for me ($52) , because B2 is negative
> B C
> B1 5 $52
> B2 (3) ($52)
>
> It is possible ?
> Thank you for your help.
>
From: tran1728 on 8 May 2010 06:34
oh Yes, thank you so much.



"JLatham" wrote:

> This can only be done with VBA and the code below will do it for you. To use
> the code, open the workbook and go to the sheet you want this to work on and
> then:
> Right-click on the worksheet's name tab and choose [View Code] from the list.
> Copy the code below and paste it into the code module presented to you in
> the previous step.
> Close the VB Editor and test it by making entries into columns B and C.
>
> Note that you must make the entry into column B before you make the entry
> into C. As written, it does not change the entry in C if you make a change
> in B after an entry is already in C. If you change B, then you would need to
> re-enter into C to see the change made.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> 'only operates when a single cell in column C changes
> If Target.Column <> 3 Or _
> Target.Cells.Count > 1 Then
> Exit Sub ' not in column C
> End If
> 'if both B# and C# have entries
> 'then make C entry same sign (+/-) as B
> If Not IsEmpty(Target) And _
> Not IsEmpty(Target.Offset(0, -1)) Then
> If Sgn(Target.Offset(0, -1)) <> Sgn(Target) Then
> Target = Target * -1
> End If
> End If
> End Sub
>
>
> "tran1728" wrote:
>
> > Dear All, pls help me.
> > when I type $ 52.00 on C1, I want C1 display positive $52 or negative ($ 52)
> > , if B1 is positive or negative, I want the entire column C.
> > Exemple :
> > C1 : I type $52, it display for me $52 , because B1 is positive
> > C2 : I type $52, it display for me ($52) , because B2 is negative
> > B C
> > B1 5 $52
> > B2 (3) ($52)
> >
> > It is possible ?
> > Thank you for your help.
> >
 | 
Pages: 1
Prev: Excel 2007 Selection Tool Broken?
Next: attachment file limit