Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation with division
Hi ,
I would like that when a number is entered in column N the person entering the column is prompted to enter in format 100,000.00 ( ex 250,225) and then once the number is entered it is converted in format 250.26 i.e. divided by 1000 and rounded off to two decimal places. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200711/1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation with division
You won't be able to do that with Data Validation alone.
Without DV just format column N as Number with 2 DP and use comma separator. When user enters 123456.78 it will become 123,456.78 To actually divide the number will have to done via event code. If using event code why not do the formatting at the same time? Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("N:N")) Is Nothing Then With Target If .Value < "" Then .Value = .Value / 1000 .NumberFormat = "#,##0.00" End If End With End If ws_exit: Application.EnableEvents = True End Sub This is sheet event code. Right-click on the sheet tab and "View Code". Copy/paste the above into that sheet module. Gord Dibben MS Excel MVP On Thu, 29 Nov 2007 15:55:31 GMT, "sidm via OfficeKB.com" <u38329@uwe wrote: Hi , I would like that when a number is entered in column N the person entering the column is prompted to enter in format 100,000.00 ( ex 250,225) and then once the number is entered it is converted in format 250.26 i.e. divided by 1000 and rounded off to two decimal places. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) | |||
Page Breaks--Controlling so no division of certain data | Setting up and Configuration of Excel |