Formatting / Condition Help ??
 

In column I, i have a data validation where they can only choose "+" or "-".
What i want is, if they choose "-" a promt will request them to enter a %.
That % then will appear in column J next to the cell they just enter "-".

It this possible in Excel? If yes, can someone teach me how to do that.

Thanks.

Formatting / Condition Help ??
 

Just paste this formula in J Column cell.

=IF(I1="-","%","")

Change the I1 cell reference to your desired cell.

If this post helps, Click Yes!

--------------------
(MS-Exl-Learner)
--------------------



"Kim" wrote:

In column I, i have a data validation where they can only choose "+" or "-".
What i want is, if they choose "-" a promt will request them to enter a %.
That % then will appear in column J next to the cell they just enter "-".

It this possible in Excel? If yes, can someone teach me how to do that.

Thanks.

Formatting / Condition Help ??
 

Sorry maybe my question wasn't correct.

What I want is when then enter "-", a promt will be requesting them to enter
a % value (1 -100). When the value is enter, it, will appear in column J.

If I put "-" in cell I2 and enter 20, then column J2 would show 20.00%.

Regards,
Kim

"Ms-Exl-Learner" wrote:

Just paste this formula in J Column cell.

=IF(I1="-","%","")

Change the I1 cell reference to your desired cell.

If this post helps, Click Yes!

--------------------
(MS-Exl-Learner)
--------------------



"Kim" wrote:

In column I, i have a data validation where they can only choose "+" or "-".
What i want is, if they choose "-" a promt will request them to enter a %.
That % then will appear in column J next to the cell they just enter "-".

It this possible in Excel? If yes, can someone teach me how to do that.

Thanks.

Formatting / Condition Help ??
 

Sorry maybe my question wasn't correct.

What I want is when then enter "-", a promt will be requesting them to enter
a % value (1 -100). When the value is enter, it, will appear in column J.

If I put "-" in cell I2 and enter 20, then column J2 would show 20.00%.

Regards,
Kim

Formatting / Condition Help ??
 

Hi Kim

With the help of VBA you can. Try the below and feedback

--Select the range of cells in Column I. Set Data Validation for the range
as + and -. In 'Error Alert' tab *** UNCHECK *** 'Show error alter after
invalid data is entered'


--Select the sheet tab which you want to work with. Right click the sheet
tab and click on 'View Code'. This will launch VBE. Paste the below code to
the right blank portion. Get back to to workbook and try out.

Private Sub Worksheet_Change(ByVal Target As Range)
Static strData As String
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("I2:I10")) Is Nothing Then
If IsNumeric(Target.Text) And strData = "-" Then
Target.Offset(0, 1).Value = Target.Text
Target = "-"
ElseIf Target.Text = "-" Then
MsgBox "Enter Percentage": Target.Activate
ElseIf Target.Text < "+" Then
MsgBox "Valid Entries are + and -": Target = "": Target.Activate
End If
strData = Target.Text
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Kim" wrote:

In column I, i have a data validation where they can only choose "+" or "-".
What i want is, if they choose "-" a promt will request them to enter a %.
That % then will appear in column J next to the cell they just enter "-".

It this possible in Excel? If yes, can someone teach me how to do that.

Thanks.

Formatting / Condition Help ??
 

Hi Jacob,

Thanks. It's almost to what I want. You know the msg box that appear, would
it be possible if there is a spare where they can enter the value. At the
moment, I've to click ok then then enter the value.

Also how can I change to number format. I've set column J as percentage, so
now if I enter 100 after the text box, it show as 10000% in J.

Regards,
Kim

"Jacob Skaria" wrote:

Hi Kim

With the help of VBA you can. Try the below and feedback

--Select the range of cells in Column I. Set Data Validation for the range
as + and -. In 'Error Alert' tab *** UNCHECK *** 'Show error alter after
invalid data is entered'


--Select the sheet tab which you want to work with. Right click the sheet
tab and click on 'View Code'. This will launch VBE. Paste the below code to
the right blank portion. Get back to to workbook and try out.

Private Sub Worksheet_Change(ByVal Target As Range)
Static strData As String
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("I2:I10")) Is Nothing Then
If IsNumeric(Target.Text) And strData = "-" Then
Target.Offset(0, 1).Value = Target.Text
Target = "-"
ElseIf Target.Text = "-" Then
MsgBox "Enter Percentage": Target.Activate
ElseIf Target.Text < "+" Then
MsgBox "Valid Entries are + and -": Target = "": Target.Activate
End If
strData = Target.Text
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Kim" wrote:

In column I, i have a data validation where they can only choose "+" or "-".
What i want is, if they choose "-" a promt will request them to enter a %.
That % then will appear in column J next to the cell they just enter "-".

It this possible in Excel? If yes, can someone teach me how to do that.

Thanks.

Formatting / Condition Help ??
 

Try the below

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("I2:I100")) Is Nothing Then
If Target.Text = "-" Then
Target.Offset(0, 1) = Format(InputBox( _
"Enter Percentage (1-100)") / 100, "# %")
ElseIf Target.Text < "+" Then
Target.Activate: MsgBox "Valid Entries are + and -": Target = ""
End If
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Kim" wrote:

Hi Jacob,

Thanks. It's almost to what I want. You know the msg box that appear, would
it be possible if there is a spare where they can enter the value. At the
moment, I've to click ok then then enter the value.

Also how can I change to number format. I've set column J as percentage, so
now if I enter 100 after the text box, it show as 10000% in J.

Regards,
Kim

"Jacob Skaria" wrote:

Hi Kim

With the help of VBA you can. Try the below and feedback

--Select the range of cells in Column I. Set Data Validation for the range
as + and -. In 'Error Alert' tab *** UNCHECK *** 'Show error alter after
invalid data is entered'


--Select the sheet tab which you want to work with. Right click the sheet
tab and click on 'View Code'. This will launch VBE. Paste the below code to
the right blank portion. Get back to to workbook and try out.

Private Sub Worksheet_Change(ByVal Target As Range)
Static strData As String
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("I2:I10")) Is Nothing Then
If IsNumeric(Target.Text) And strData = "-" Then
Target.Offset(0, 1).Value = Target.Text
Target = "-"
ElseIf Target.Text = "-" Then
MsgBox "Enter Percentage": Target.Activate
ElseIf Target.Text < "+" Then
MsgBox "Valid Entries are + and -": Target = "": Target.Activate
End If
strData = Target.Text
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Kim" wrote:

In column I, i have a data validation where they can only choose "+" or "-".
What i want is, if they choose "-" a promt will request them to enter a %.
That % then will appear in column J next to the cell they just enter "-".

It this possible in Excel? If yes, can someone teach me how to do that.

Thanks.

Formatting / Condition Help ??
 

Thanks you very much. Is perfect now.

"Jacob Skaria" wrote:

Try the below

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("I2:I100")) Is Nothing Then
If Target.Text = "-" Then
Target.Offset(0, 1) = Format(InputBox( _
"Enter Percentage (1-100)") / 100, "# %")
ElseIf Target.Text < "+" Then
Target.Activate: MsgBox "Valid Entries are + and -": Target = ""
End If
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Kim" wrote:

Hi Jacob,

Thanks. It's almost to what I want. You know the msg box that appear, would
it be possible if there is a spare where they can enter the value. At the
moment, I've to click ok then then enter the value.

Also how can I change to number format. I've set column J as percentage, so
now if I enter 100 after the text box, it show as 10000% in J.

Regards,
Kim

"Jacob Skaria" wrote:

Hi Kim

With the help of VBA you can. Try the below and feedback

--Select the range of cells in Column I. Set Data Validation for the range
as + and -. In 'Error Alert' tab *** UNCHECK *** 'Show error alter after
invalid data is entered'


--Select the sheet tab which you want to work with. Right click the sheet
tab and click on 'View Code'. This will launch VBE. Paste the below code to
the right blank portion. Get back to to workbook and try out.

Private Sub Worksheet_Change(ByVal Target As Range)
Static strData As String
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("I2:I10")) Is Nothing Then
If IsNumeric(Target.Text) And strData = "-" Then
Target.Offset(0, 1).Value = Target.Text
Target = "-"
ElseIf Target.Text = "-" Then
MsgBox "Enter Percentage": Target.Activate
ElseIf Target.Text < "+" Then
MsgBox "Valid Entries are + and -": Target = "": Target.Activate
End If
strData = Target.Text
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Kim" wrote:

In column I, i have a data validation where they can only choose "+" or "-".
What i want is, if they choose "-" a promt will request them to enter a %.
That % then will appear in column J next to the cell they just enter "-".

It this possible in Excel? If yes, can someone teach me how to do that.

Thanks.

Formatting / Condition Help ??
 

Hi Jacob,

I've slightly change the column, so I thought by changing the range it would
be ok. But it's not. :( Can you see what's wrong with the code ?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("K8:K29")) Is Nothing Then
If Target.Text = "-" Then
Target.Offset(0, 1) = Format(InputBox( _
"Enter Percentage (1-100)") / 100, "# %")
ElseIf Target.Text < "+" Then
Target.Activate: MsgBox "Valid Entries are + and -": Target = ""
End If
End If
Application.EnableEvents = True
End Sub

Thanks.

"Jacob Skaria" wrote:

Try the below

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("I2:I100")) Is Nothing Then
If Target.Text = "-" Then
Target.Offset(0, 1) = Format(InputBox( _
"Enter Percentage (1-100)") / 100, "# %")
ElseIf Target.Text < "+" Then
Target.Activate: MsgBox "Valid Entries are + and -": Target = ""
End If
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Kim" wrote:

Hi Jacob,

Thanks. It's almost to what I want. You know the msg box that appear, would
it be possible if there is a spare where they can enter the value. At the
moment, I've to click ok then then enter the value.

Also how can I change to number format. I've set column J as percentage, so
now if I enter 100 after the text box, it show as 10000% in J.

Regards,
Kim

"Jacob Skaria" wrote:

Hi Kim

With the help of VBA you can. Try the below and feedback

--Select the range of cells in Column I. Set Data Validation for the range
as + and -. In 'Error Alert' tab *** UNCHECK *** 'Show error alter after
invalid data is entered'


--Select the sheet tab which you want to work with. Right click the sheet
tab and click on 'View Code'. This will launch VBE. Paste the below code to
the right blank portion. Get back to to workbook and try out.

Private Sub Worksheet_Change(ByVal Target As Range)
Static strData As String
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("I2:I10")) Is Nothing Then
If IsNumeric(Target.Text) And strData = "-" Then
Target.Offset(0, 1).Value = Target.Text
Target = "-"
ElseIf Target.Text = "-" Then
MsgBox "Enter Percentage": Target.Activate
ElseIf Target.Text < "+" Then
MsgBox "Valid Entries are + and -": Target = "": Target.Activate
End If
strData = Target.Text
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Kim" wrote:

In column I, i have a data validation where they can only choose "+" or "-".
What i want is, if they choose "-" a promt will request them to enter a %.
That % then will appear in column J next to the cell they just enter "-".

It this possible in Excel? If yes, can someone teach me how to do that.

Thanks.

Formatting / Condition Help ??
 

If you still want the % number in column J change the offset from column K

Target.Offset(0, -1)

Offset works like this. Offset(row, column)

0 is same row.....................-1 is column to left of target


Gord Dibben MS Excel MVP

On Thu, 24 Sep 2009 06:58:02 -0700, Kim
wrote:

Hi Jacob,

I've slightly change the column, so I thought by changing the range it would
be ok. But it's not. :( Can you see what's wrong with the code ?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("K8:K29")) Is Nothing Then
If Target.Text = "-" Then
Target.Offset(0, 1) = Format(InputBox( _
"Enter Percentage (1-100)") / 100, "# %")
ElseIf Target.Text < "+" Then
Target.Activate: MsgBox "Valid Entries are + and -": Target = ""
End If
End If
Application.EnableEvents = True
End Sub

Thanks.

"Jacob Skaria" wrote:

Try the below

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("I2:I100")) Is Nothing Then
If Target.Text = "-" Then
Target.Offset(0, 1) = Format(InputBox( _
"Enter Percentage (1-100)") / 100, "# %")
ElseIf Target.Text < "+" Then
Target.Activate: MsgBox "Valid Entries are + and -": Target = ""
End If
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Kim" wrote:

Hi Jacob,

Thanks. It's almost to what I want. You know the msg box that appear, would
it be possible if there is a spare where they can enter the value. At the
moment, I've to click ok then then enter the value.

Also how can I change to number format. I've set column J as percentage, so
now if I enter 100 after the text box, it show as 10000% in J.

Regards,
Kim

"Jacob Skaria" wrote:

Hi Kim

With the help of VBA you can. Try the below and feedback

--Select the range of cells in Column I. Set Data Validation for the range
as + and -. In 'Error Alert' tab *** UNCHECK *** 'Show error alter after
invalid data is entered'


--Select the sheet tab which you want to work with. Right click the sheet
tab and click on 'View Code'. This will launch VBE. Paste the below code to
the right blank portion. Get back to to workbook and try out.

Private Sub Worksheet_Change(ByVal Target As Range)
Static strData As String
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("I2:I10")) Is Nothing Then
If IsNumeric(Target.Text) And strData = "-" Then
Target.Offset(0, 1).Value = Target.Text
Target = "-"
ElseIf Target.Text = "-" Then
MsgBox "Enter Percentage": Target.Activate
ElseIf Target.Text < "+" Then
MsgBox "Valid Entries are + and -": Target = "": Target.Activate
End If
strData = Target.Text
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Kim" wrote:

In column I, i have a data validation where they can only choose "+" or "-".
What i want is, if they choose "-" a promt will request them to enter a %.
That % then will appear in column J next to the cell they just enter "-".

It this possible in Excel? If yes, can someone teach me how to do that.

Thanks.