How to define cells so that only numbers can be filled?

Hi,

How could I format cells in excel so that only numbers can be filled
The input should be integer e.g. between 0...99 and cells shoud no
accept any other marks, such as space, letters... simply nothing els
but integers.

Thanks

--
Message posted from http://www.ExcelForum.com

0
1/30/2004 8:18:58 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
770 Views

Similar Articles

[PageSpeed] 35

Hi Juha!

Try:
Data > Validation

You'll find it pretty intuitive and very flexible as to what you can
do as far a restricting input to a cell.

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


0
njharker (1646)
1/30/2004 8:29:35 AM
Select the cells you want this restriction to apply to and then select 
Data/Validation ( from the menu ). Select Allow Whole Numbers and set
the minimum and maximum values. You can even include an input message (
which appears when the cell is selected ) and an error message.


---
Message posted from http://www.ExcelForum.com/

0
1/30/2004 8:29:41 AM
On Fri, 30 Jan 2004 02:18:58 -0600, Juha
<<Juha.10u5hk@excelforum-nospam.com>> wrote:

>How could I format cells in excel so that only numbers can be filled?
>The input should be integer e.g. between 0...99 and cells shoud not
>accept any other marks, such as space, letters... simply nothing else
>but integers.

You can go to the Data menu, Validation item and set validation so
that only whole numbers between 0 and 99 are allowed.

Be aware that this is NOT foolproof, and will not prevent a user (for
example) pasting an invalid value from another cell into your entry
cell. However it should do for most purposes.

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *
0
ApolloXVIII (173)
1/30/2004 8:29:53 AM
:)  Thanks!! It works perfectly!

--
Message posted from http://www.ExcelForum.com

0
1/30/2004 8:35:54 AM
Juha,

If you go into Data>Data Validation, you will see that there are options
there to specify whole or decimal numbers, and you can set limits.

Other than that, it is probably VBA.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Juha >" <<Juha.10u5hk@excelforum-nospam.com> wrote in message
news:Juha.10u5hk@excelforum-nospam.com...
> Hi,
>
> How could I format cells in excel so that only numbers can be filled?
> The input should be integer e.g. between 0...99 and cells shoud not
> accept any other marks, such as space, letters... simply nothing else
> but integers.
>
> Thanks!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
1/30/2004 9:10:54 AM
Reply:

Similar Artilces:

Page numbers are not recognizing my headings
Hi! I just created an outline using headings so that I could have page numbers that include my chapter number. However, when I try to format my page number to include chapter numbers, it doesn't change how my page numbers appear (ie, it should say 3-1, but instead it just says 1). Any ideas on why this might be? Thanks! See http://www.shaunakelly.com/word/numbering/UseBuiltInHeadingStyles.html Batraygirl wrote: >Hi! > >I just created an outline using headings so that I could have page numbers >that include my chapter number. However, when I try to for...

linked formatting? link color of cells on two tabs
i have two tabs on one worksheet -- one neatly formatted template page pulls data from a sloppy "raw data" tab. the problem is this -- the "raw data" tab has numbers that the system outputs colored green or red for statistical significance. is there a way to pull the color-formatting off the raw data tab onto the template tab? Right now I am doing this all manually on a dozen reports and it takes forever. There has got to be some way to systematize this to save time. Thanks Jason G Use conditional formatting and reference the conditions to cells in the form...

blank cell turns to 0
Hi Guys, Using Excel 2000. I am trying to work on a sheet someone else started but can't finish. In a cell on the sheet that the formula below is pulling the data from there is a blank field but there is a 0 in the cell that has this formula in it on my new sheet. If the cell on the first sheet is blank, I want the cell that equals that cell on my second sheet to be blank too. ='SVMMC-admin, tx, blue cards'!$B$3 The sheet is a yes/no sheet and when there is a 1 in the cell it means yes, when there is a 0 in the cell it means no, when the cell is blank, it means Not Appl...

How to not copy the numbering from numbered list in Word ?
Hi ! I want to copy a numbered list in Word and paste to a text file. The numbers show up. How do I not copy the numbers preceding the items ? Thanks very much in advanced. By temporarily disabling the numbers and bullets in word before you copy and paste the text. Then you can simply close the word document (without saving it) so that no changes in the word file are saved. That one time call girl may suggest that you should use Tipex on the screen to remove the numbers!!! You have been warned. hth dingdongdingding wrote: > > Hi ! > I want to co...

How can I link a SRS Report with the Account?
I have made a Report that list the 5 major types of Accounts and when the user clicks a Account I would like to open the Account. How can I put a Link in a Table Details Section on the SRS Reports? -- Microsoft Business Solutions Certified Professional–Microsoft CRM Applications Professional Microsoft Business Solutions Certified Professional–Microsoft CRM Customization If you have the SDK help file, this explains how this can be done - in the Reports section. In summary though, this is what you do: 1. Create a hidden Parameter of type Text 2. Find the URL of the record type you want...

How do I set a colour to 4 cells based on the value of a cell
I want to assign a colour to a group of 4 cells (ie a1 to a4) based on the colour that is typed into another cell (ie d1). It could be that I need the option of using 10 colours. eg if d1 was "green" then a1-a4 would be coloured green. Andy For greater than 3 colors(4 if use default no color) you will need VBA. Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set rng1 = Intersect(Target, Range("D1")) If rng1 Is Nothing Then Exit Sub On Error GoTo endit Application.Enab...

Reference to cell with external spreadsheet
I have a lookup function with a very long range name which referrs to another spreadsheet. I would like to make a cell with the text of the range and just refer to the cell in the lookup function. Is this possible? Thank you On 19 Dec, 20:43, B Lane <B L...@discussions.microsoft.com> wrote: > I have a lookup function with a very long range name which referrs to ano= ther > spreadsheet. I would like to make a cell with the text of the range and j= ust > refer to the cell in the lookup function. Is this possible? > > Thank you If you long range name is in ...

How can I change bg color in SDI?
Hi all.. How can I change bg color in SDI? I know that I can do it by handling WM_ERASEBKGND message My code look like this BOOL CSomthingView::OnEraseBkgnd(CDC* pDC) { CBrush br(RGB(127,127,127)); CRect rc; GetClientRect(&rc); pDC->FillSolidRect(&rc,&br); .... .... return TRUE; } But when this code is called.. Windows fliker.. How can I change that code..? What I thought is that I can do it by changing WNDCLASS's hbrBackground But I have no idea how to do it? Is there better way to do it? "EunSub,Kim" <imkimes@verizon.net> wrote > Hi all.. >...

Collections Management
Dynamics GP v9 - Collections Management Users are unable to use "user defined letters" (Word Documents) for mass mailings. They are limited to only using predefined letters, or printing each letter individually for every customer. Error received when users attempt to use a custom letter: -------------------------------------------------- Unhandled script exception: Cannot find report "COL_Reminder_UpcomingDue". EXCEPTION_CLASS_SCRIPT_MISSING SCRIPT_CMD_REPORT -------------------------------------------------- ---------------- This post is a suggestion for Microso...

How many pages can a booklet have?
Hi how many pages can a booklet have? Is there a limit to the pages in a publisher booklet. Each page has three photos. There is no limit, your RAM may decide for you. What version Publisher? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Verystumped" <Verystumped@discussions.microsoft.com> wrote in message news:8F2778CF-7B14-4BDD-93C5-EF9B39E2C1A7@microsoft.com... > Hi how many pages can a booklet have? Is there a limit to the pages in a > publisher booklet. Each page has three photos. ...

Can't see printers in directory
Hello, The shared and network printers aren't showing up in the directories of the workstations. On XP machines, the network printer does show, but no shared printers. On Windows 7, no printers show. I need to get this resolved. Thanks for any help and God Bless, Mark A. Sam Hello Mark, Which directories are you talking about? How did you add the printers to the machines? Please describe some more details. Best regards Meinolf Weber Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights. ** Please do NOT email, ...

Application-defined or object-defined error
Hi, I find myself stumped by an incredibly easy piece of code and one that I have used before. I am getting the error: Run-time error '1004': Application-defined or object-defined error I am getting the error when I run the following code: Private Sub Workbook_Open() With Application .ScreenUpdating = False .DisplayAlerts = False Workbooks.Open "\\depot02\rel\www\internal\business_areas\edg\Metrics\Phones\HighHoldTimesDetailed.xls" Workbooks("HighHoldTimesDetailed.xls").Worksheets("data").Cells.Copy _ Workbooks("phoneholdtime...

How can I apply holidays to a public calendar?
We have a shared office calendar to which I post staff vacations, office closed days, etc. (I'm the administrator). I can't get holidays applied to that calendar. Any ideas? Thanks, Mary Apply them to your own Calendar and then switch your view to By Category. you can now easily copy or move the Holiday entries to the Public Folder. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----...

Deleting part of a cell
I have a large number of cells containing text (name of co. or individual, varying text string length) followed by a space and an 18 character string such as this "Shaving Centers </0016000000IMo40>" - I wold like to retain only the initial text string (ie. "Shaving Centers" - without the quotes of course) whether the space remains is immaterial. Problem is the </*> part is not constant, though I think it is of uniform length. I tried using the "IF(Right(..." function but had no success using the substitution for the numbers ie, </*>. If unclear ...

runtime error 2465: application-defined or object-defined error
I'm trying to use a button on a main form to change the sort order of a sub- subform.When I execute the following, I get runtime error 2465: application- defined or object-defined error. opting to debug, I find the line with orderbyon highlighted. Anybody see what's wrong? Private sub timesort_click() Forms![control]![worklist]![approved].Form.OrderBy = "Forms![control]! [worklist]![approved]![time]" Forms![control]![worklist]![approved].Form.OrderBy0n = True End Sub -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/a...

how to do self-defined regression in excel?
Hi, I want to do regression ananlysis in excel using a sigmoid function, which is not offered in the general 6 functions in excel(by adding trendline). How can I do it? Thanks. The Analysis ToolPak - VBA addin has a regression function and probably everything else you want. Tools > AddIns then checkmark Analysis ToolPak - VBA Go back to Tools dropdown and it should be listed near the bottotm of the dropdown. >-----Original Message----- >Hi, I want to do regression ananlysis in excel using a >sigmoid function, which is not offered in the general 6 >functions in excel(by a...

Exponential number format
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I'm trying to write exponential formulas in excel 2008, but it doesn't recognize the symbol ^ that we use on excel for windows. <br> example: <br> Excel Windows : = 2^2 -> result: 4 <br><br>Excel Mac : = 2^2 -> result: error <br><br>When we import some sheet from windows to mac with this formula works, but when I try to create on mac not recognize. <br><br>My question is: There is any other way to use this Symbol, for this kind of formula? The sam...

Cell Address in a pivot table
I have a pivot table which gets updated every day. Last row in the pivot table is 'Grand Total' and the number of rows in the pivot table varies every day. I have another function which needs the count of number of rows in the pivot table. Since the starting cell remains the same, I am counting the number of rows in the pivot table = Row Number of 'Grand Total' - Starting Cell value. Now my question is .. how do I get the Row Number of cell 'Grand Total'? Thank you for your time. Regards, Reddy ------------------------------------------------ ~~ Message posted ...

Simple "if word exist among theese cells, then true"
Im banging my head on the "find" functions. They give me a -value- when I need TRUE/FALSE for an IF function. It must be a much easier way to return TRUE or FALSE if a word exist within an area: Data: A1: Orange A2: Apple A3: Banana Function: 1: Is "*Apple*" in A1:A3? = TRUE! 2: Is "*Tomato*" in A1:A3? = FALSE! -- JemyM ------------------------------------------------------------------------ JemyM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26945 View this thread: http://www.excelforum.com/showthread.php?threadid=517716 =N...

how can i auto text in excel
-- how can i auto text in excel Sue I assume you mean that you want AutoComplete. Go to Tools, Options, Edit and select the box at the bottom: Enable AutoComplete for Cell Values. To set up things to replace (for example I have my version replacing my initials with my name), go to Tools, AutoCorrect Options and set things up in there. HTH David "Sue" wrote: > > -- how can i auto text in excel > Sue ...

Adding Comments From Cells To Chart Points.
Hi all I have spent a large portion of my weekend trying to find the answer to this. Without much luck! I have finally come up with my own code and was hoping someone could have a look at it. I'm looking to clean it up and make it a little more efficient. Thanks in advance, Greg. Sub AddCommentsToChartPoints() Dim ws As Worksheet Dim ct As ChartObject Dim serSeries As SeriesCollection Dim ser As Series Dim Counter As Integer Dim ChartName As String Dim xVals As String Dim xAddress As String 'Loop through each worksheet in workbook For Each ws In Worksheets 'Lo...

Defining a variable to = MATCH
Dim thingy As Integer thingy = "=MATCH(""DiceC"",qperiodagentperformance!A:A,0)" ActiveCell.Formula = _ "=INDEX(qperiodagentperformance!D" & thingy & ":D13000,MATCH(""Agen Summary"",qperiodagentperformance!A" & thingy & ":A13000,0))" Range("D26").Select my thingy doesnt seem to work..... no comment Any suggestions on correcting this? Mik -- Message posted from http://www.ExcelForum.com Try with thingy = Application.Match("DiceC", Sheets("qperiodagentperformance"...

Can i setup outlook local(.pst) on citrix diff from my LAN?
I work on different network than my company LAN.Today we access outlook through web access.But this doesn't give me option for local .PST saving. Is it possible to have local mailbox in this situation? If so what needs to be done? Thank You Rambabu Try asking the guys that support the Exchange server if they support RPC over HTTPS (aka Outlook Anywhere). If they say yes, then ask them if they would help you setup Outlook 2003/2007 on your machine. "Rambabu" <Rambabu@discussions.microsoft.com> wrote in message news:15C16257-E787-4B11-A238-C8E61590DD8...

Splitting Cells
Im trying to enter two values into the same cell. I've seen it done, it has a red diagonal line seperating the two values. Thanks for any help. -- MFFC2005 ------------------------------------------------------------------------ MFFC2005's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25816 View this thread: http://www.excelforum.com/showthread.php?threadid=392130 First format the cell as Text. Then enter the two values separated by any character you desire. The cell will hold exactly what you typed. -- Gary's Student "MFFC2005" wro...

User-Defined type not defined?
I recently imported all my data into a blank DB now I am getting this error on: Dim wrk As Workspace Is that something to do with me importing into a new DB Thanks for any help...........Bob Sub SelAllNone(Optional SelectAll As Boolean = True) On Error GoTo stoprun Dim sqlStr As String Dim wrk As Workspace Dim db As Database Set wrk = DBEngine.Workspaces(0) Set db = CurrentDb sqlStr = "UPDATE [tblHorseInfo] SET [Worksheet] = " & SelectAll & ";" wrk.BeginTrans db.Execute sqlStr, dbFailOnError wrk.CommitTrans Exit_Here: Set wrk = Nothing Set db = Nothing ...