Validation for Numeric values

I want to add validation to a cell as follows:

Cell is formatted as Accounting, 2 decimal places
Cell can have either whole nunmbers, or decimal entered into it
However, some staff are also entering text
So, what I want to do is, add validation to the cell that will accept
a numeric value, with or without decimals, and prohibit the entry of
anything else.

Can it be done?

TIA

Duncs
0
2/26/2009 12:51:44 PM
excel 39879 articles. 2 followers. Follow

3 Replies
453 Views

Similar Articles

[PageSpeed] 14

Select the cell; Use Data | Data Validation; specify Allow Decimal
Optionally, open the other two tabs on the Data Validation dialog to add 
messages for the fools who type text. Typing text will now cause the PC to 
emit a 'bong' - at least that is how mine is set!

best wishes from New Scotland (Nova Scotia, Canada, ye ken)
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Duncs" <True.Kilted.Scot@gmail.com> wrote in message 
news:db33e618-4971-4277-9367-8ed422e01b98@m4g2000vbp.googlegroups.com...
>I want to add validation to a cell as follows:
>
> Cell is formatted as Accounting, 2 decimal places
> Cell can have either whole nunmbers, or decimal entered into it
> However, some staff are also entering text
> So, what I want to do is, add validation to the cell that will accept
> a numeric value, with or without decimals, and prohibit the entry of
> anything else.
>
> Can it be done?
>
> TIA
>
> Duncs 


0
bliengme (657)
2/26/2009 1:21:25 PM
Select Decimal

Duncs wrote:

> I want to add validation to a cell as follows:
> 
> Cell is formatted as Accounting, 2 decimal places
> Cell can have either whole nunmbers, or decimal entered into it
> However, some staff are also entering text
> So, what I want to do is, add validation to the cell that will accept
> a numeric value, with or without decimals, and prohibit the entry of
> anything else.
> 
> Can it be done?
> 
> TIA
> 
> Duncs

0
birelan (531)
2/26/2009 1:22:24 PM
Thanks to you both.  I'm sure I selected that before, and it wouldn't
let me enter whole numbers.

I must have imagined that though! :)

Duncs

On 26 Feb, 13:22, Bob I <bire...@yahoo.com> wrote:
> Select Decimal
>
>
>
> Duncs wrote:
> > I want to add validation to a cell as follows:
>
> > Cell is formatted as Accounting, 2 decimal places
> > Cell can have either whole nunmbers, or decimal entered into it
> > However, some staff are also entering text
> > So, what I want to do is, add validation to the cell that will accept
> > a numeric value, with or without decimals, and prohibit the entry of
> > anything else.
>
> > Can it be done?
>
> > TIA
>
> > Duncs- Hide quoted text -
>
> - Show quoted text -

0
2/26/2009 3:25:33 PM
Reply:

Similar Artilces:

Msgbox entry validation
The following Macro will not loop more than twice the error entries. Will someone help me correct it to loop indefinely? Thanks Sub Entry_validation() Dim Teststring As String Dim Inputstring As String Cells(1, 1) = "ABC" 'as given filename Cells(2, 1) = "DEF" 'as given filename Cells(3, 1) = "GHI" 'as given filename Cells(4, 1) = "JKL" 'as given filename errorloop: Inputstring = InputBox(prompt:="Enter Filename" & vbLf & "Enter abort to abort en...

Data Validation -> Validation list is larger than the cell width
I have a workbook that I've just added data validation to one cell. FWIW, the data validation is based on a named range. Here's the (perceived) problem. When I've set up data validation in the past, the selection is the same width as the cell. In this case, the list starts almost a full cell width to the left. What am I missing? Thanks, Barb Reinhardt Barb, In Excel2003 I can replicate what you describe under the following conditions: 1. the named range contains entries that are wider than the data validation cell 2. The data validation cell is r...

CPrintDialog default values
I am trying to set the default values of the CPrintDialog in my 'InPreparePrinting' method of CMyView. The problem that I have is that the 'To Page' always defaults to 'MaxPages' no matter what I do. I have tries setting the 'm_pd.nToPage' to a value but it does not set the default. I would like to default the 'To Page' to the value 'MaxPages - 1'. Can the 'To Page' be changes without changing 'MaxPages'????? -- Hank Williams Quantum Technologies, Inc. HaWilliams(at)spamcop.net When I add '1' to 'nToPage'...

Validating against a DTD
Hello All, I am trying to validate an xml file against a DTD. I went through a lot of examples especially: http://www.xmlforasp.net/codebank/util/srcview.aspx?path=../../codebank/System_Xml/XmlValidatingReader/Validator/validator.src&file=validator.cs&font=3 but its not working in my case. First of all, when i try to add my DTD file in XmlSchemaCollection object, it throws an error saying DocType element is expected. I am using NITF DTD for this and there is no DocType element in it. Any help will be appreciated. Thanks. VD You should not add DTDs to XmlSchemaCollection. XmlSchema...

find a record with a missing value
Hi everybody, I have an excel worksheet A B John XY John X John Y John G Ed X Ed Y Tod XY .... GHA in each records I have a name in column A and some value in column C , I want to write a fomula ( in column D ) to find every record ( an individual ) that doesn't have at least one XY in "column C" , emplyees are sorted by thier names and there are variable number of reocords for each employee. In this example Ed should be flagged because it doesn't have at leas...

Delete rows based on a cell value in the row
I'd like to delete rows from a spreadsheet based on the value of a cell in a row. This is a very large spreadsheet (60,000+ lines). I want to delete rows based on a value (in this case all parts which are purchased- vs mfd) without destroying the spreadsheet structure. In this case I have several bills of material where I want to list all the assemblies, but not the purchased parts. It seems it should be possible, but I'm not having any luck with the filter method. Thanks, Dave sort?? or use a macro to delete, from the bottom up, if the cell contains your text -- Don Gu...

Data Validation
Hi all I want to set data validation on a cell so that it will accept the following (and only the following) text strings: (1) "BR" (literally) or (2) "NT" (literally) or (3) "nA" where n is any positive integral numerical value including zero and A may take any of the values "L", "P", "T", "V" or "Y" or (4) "An" where n is any positive integral numerical value including zero and A may take either of the values "K" or "D". Is this possible, please, and if so how? thanks -- Return e...

Entering a Formula / Wrong value
I have inserted a new column into the work sheet called column D, I have named the cell D1 Service. In cell D2 I enter the formula =(TODAY()-E2)/365 and should come up with the Value 4.9 in D2 and them drag the fill handle down 14 cells to compute the rest of the length in the new column D. But in cell D2 I keep getting the Value 9.58989, I have tried reducing the # of decimals and also widening the with of the new column D, but with no luck. As a new Excel user can someone give me a tip on how to trouble shoot this situation? I get 4.9 if E2 contains 31 December 2002 or 9.58989 if...

Validation dropdown list is not coming visible
Hi, I'm using XP and 2003. I have a problem that I have once found resolution, but now it came again and cannot remember what needs to be done. Here is the problem: There is a column where Validation is specified in right manner with error message in case of error. When selecting the cell - dropdown list is not coming visible. In case I write something wrong it gives error message. If I recall correctly - it was something to do with some kind of list. How can I change this to work properly? I tried to paste new column from another file where the column is working as I want...

complex query to pull unique values
I have an inspection table to keep track of rooms in various buildings. These rooms are shared with different supervisors. One inspection of a location would result in x # of inspection records. If the room was shared by 3 supervisors, the same inspection would generate 3 inspection records. Comment field on each record would be different according to the supervisor, were their employees following protocol, safety issues etc. Since there are so many fields, trying to pull unique values is difficult, so I made a new field in the query which concatenates SupervisorID, Bldg & ...

What value (from a function) would a chart line ignore (like it ignores an empty cell)?
When creating a line chart, I would like to show separate bits of a line (i.e. a discontinous line). The underlying data comes in the form of excel formulas. Neither "" or FALSE does the job (both render a zero-value when charting). I would like to avoid running a macro that deletes the appropriate cells. Is there any solution for this in today's excel version? Many thanks for your help! You can use NA() in the formula, e.g.: =IF(A5=0,NA(),A5) Hansi wrote: > When creating a line chart, I would like to show separate bits of a > line (i.e. a discontinous line). The unde...

unable to enter numerical data in cells
I Have just activated excel 2003. I am unable to enter data into any cells. I am also unable to enter information into microsoft word. How do I rectify this problem? I am familiar with excel 2000 and never encountered any problems. What happens when you try to enter data in cells or in Word? Error messages? Nothing? From where did you install MS Office 2003? Retail CD? Trial CD? Gord Dibben MS Excel MVP On Fri, 7 Jul 2006 08:35:02 -0700, Genek185 <Genek185@discussions.microsoft.com> wrote: >I Have just activated excel 2003. I am unable to enter data into any cells....

reinstall-validation problem
Hardware hit from lightning so new hd and video card and start over. All goes well with install using a slipstreamed XP-Pro SP3 disk but I notice it never asks for the Product Key during install yet it Activates itself during one of the boots and I think little of it until ... During the updates which followed (100+!!!) I notice Windows Defender doesn't seem to run or leave itself installed. As it is running on my laptop figured I just download it. When I run it, it wants to first do the Genuine Advantage bs which I let it, at which time it says "bogus". Ugh, g...

Validation
Julie the same can be achieve without going thru the double drop down boxes. You could do it just using the combobox alone. Thank again Please stay in the ORIGINAL thread. The archives will thank you. -- Don Guillett SalesAid Software donaldb@281.com "JLong" <anonymous@discussions.microsoft.com> wrote in message news:034101c49c3b$889b5020$a501280a@phx.gbl... > Julie the same can be achieve without going thru the > double drop down boxes. You could do it just using the > combobox alone. Thank again ...

validating macro
Hi I am sending a spread sheet out to branchs I need them to complete a sheet (common info) before they do anything else on the workbook. I am looking for a macro or code for VBA which who check (common info) see if the cells are complete if not bring up a error screen informing the user to complete the form and then take them straight to the (common info) sheet. and if possible let them go to the tab which they want to go to. Use an extended version of something along these lines: (if there are lots of cells use a 'for row numbers x to y' or 'do until' statment, o...

How to remove comma and decimals from a value
How can I remove comma's and decimal's from a value using a formula in a query? i.e. 4,325.00 Result: 432500 Assuming Excel recognizes the data as numeric, just reformat it. Edit > Cells, select the Number tab, choose 0 decimals and uncheck the separator box. If there not recognized as numbers, you'd first want to convert them: type a 0 in some random cell, copy it, select your data set and Edit > Paste Special, select Values and Add, then click OK. "Send Object Command - Two attachments" wrote: > How can I remove comma's and decimal's from a valu...

Data Validation
I have set up a data form and have data validation rules on the various input cells. I see that there is an option called "Ignore blanks" in the data validation settings, which is supposed to restrict someone from entering past an input cell without entering valid data, if the "Ignore blanks" box is unchecked. I have tried to do this, but it does not seem to work. Please could someone tell me what I am doing wrong? Provide information, or better yet, a screen capture of the Data Validation dialog box with the Setting tab selected (User ALT+PrintScreen to copy the imag...

Reboot Validator
I'm running XP Pro, SP3. Attempting to install a program from Nero. Software keeps failing at startup, despite numerous re-installs. During installs, it keeps attempting to update the 'reboot validator' which it does not seem to be able to do. What is the reboot validator, and how do I update it. Couldn't find anything useful on MS site and Nero Tech support just says its an MS problem. -- Thank you, B. Parker Never heard of it but Nero Support should know about it: Reboot Validator appears to be a Nero component; cf. http://www.brighthub.com/comput...

How Do I Calculate My Total Stock Value At A Specific Date?
I'd like to be able to calculate how much stock I had at a paticular date/time in the past. Eg. What was the value of stock I had on the 1st of January? How can i do that? Thanks, Leigh. There is no direct report that can provide you with the stock on hand from a previous date. But there are 2 reports whereby the difference will assist with what you are trying to achieve. You can see all item movement for lets say now to last week and remove this value from the current item value 'should' in thery provide you with a previous date stock value. Hope this helps. Ivan ...

Conditional validation?
Hello All I have a sheet that is essentially just columns of data (except for the column headings in the first row), and two of the columns are what I would call 'mutually exclusive': i.e. if a value is entered in a row in either column, the user must be prevented from entering any value in the same row of the other column. The two columns already have some basic validation applied (ranges of permissable numerical values), and this validation would need to be applied for any values that are entered. I am sure this can be done, but cannot see how! Hope someone can help. Many thanks L...

Update value. Function
Hello, I created a simple function on VBA as follows: Function Test() As Integer Dim I As Single For I = 17 To 24 Test = (Test + 1) * Range("Examples_1_a_4!B" & I) Next I End Function On my Excel sheet I have on a cell the following: =Test() It works but when I change a value in the range I need to go to the result cell and click enter to the value be updated. Why? Thanks, Miguel Hi Miguel You have to make you funciton volatile, to do so, add this as first line in you sub: Application.Volatile Regards, Per "shapper" &...

REPOST: dynamic validation
I think I wasn't clear that I need the Validation option under the Data menu, List, source values. That is what I am trying to fill with this equation. In other words, range1 and range2 are sets cells which contain values. I want those values to be the dropdown options in the cell based on what the previous cell to the left is selected as. So if Range1 is three cells with Tall, Dark, Handsome and Range2 is Sweet, Pretty, Nice, then based on the selection of F or M in A1, I want the choices for B1 to be one of those lists. I know that quotes are required for the IF. My probl...

Excel 2000 vs 2002 Sheet protection & Validation cells
Hello, I have an Excel spreadsheet built on Excel 2000. I have set sheet protection with password, and I have some unprotected cells with drop down menu (validation with 50 choices) Everything works very well. I sent this file to a friend who has Excel 2002. The same "unprotected" validation cells cannot be modified in his version as the message says that these cells are protected What can be done in order to make it work on his version?? I do have some "very hidden" sheet with password protected VBA properties, if that is an issue. Similarly there are some macros o...

How to use data validation
[NB: I re-structure my question since people got misled from my previous one] What I really wish to ask is: I wish to use data validation for a particular cell. But it doesn't work if I choose "custom" in the "allow" box. 1) In Data | Validation | Settings Tab | Validation Criteria, there is an "Allow" Box. 2) I chose "custom", there is a formula. 3) I type in some formula, eg: =J3 (Just Reference) =J3-J1 (Reference-type formula) =round(J1) (Function-type formula) None of them can activate the validation fu...

Email Validation
I was testing this email validation rule. Is Null Or ((Like "*?@?*.com") Or (Like "*?@?*.org") Or (Like "*?@?*.net") Or (Like "*?@?*.mil") Or (Like "*?@?*.US") And (Not Like "*[ ,;]*" And Not Like "*.@*")) ...but noticed I was able to input .@any.com ... e.g., I was trying to prevent user typing a .@ [that is a dot@] using the ... And Not Like "*.@*")) .. but it must be getting cancelled out by something previous in the validation string. Any suggestions? You could use a routine such as: P...