Formatting / Condition Help ??

  • Thread starter Thread starter Kim
  • Start date Start date
K

Kim

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.
 
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!
 
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
 
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
 
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
 
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
 
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
 
Thanks you very much. Is perfect now.

Jacob Skaria said:
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
Click to expand...
 
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.
 
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
 
Back
Top