| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Condition Formatting based on If Then | Excel Worksheet Functions | |||
| Adding a second formatting condition. | Excel Worksheet Functions | |||
| Condition formatting & Formulas | Excel Worksheet Functions | |||
| VLOOKUP / Condition Formatting Help | Excel Discussion (Misc queries) | |||
| IF Function with formatting condition | Excel Worksheet Functions | |||
Linear Mode
