Data Validation on Cells #2

How can I restraint the user from entering odd numbers in the cell? But I need the outcome to be in multiples of 5?

Ringo Tan
Tan (28)
8/5/2004 1:37:04 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 58

So you want to restrict entries to even multiples of 5?

Assuming your input cell is A1:

Allow:      Custom
Formula:    =MOD(A1,10)=0

In article <>,
 ringo tan <ringo> wrote:

> How can I restraint the user from entering odd numbers in the cell? But I 
> need the outcome to be in multiples of 5?
> Thanks.
> Ringo Tan
jemcgimpsey (6723)
8/5/2004 1:56:36 AM
or do you, once the person has entered the (even) number round the number to
the nearest multiple of 5?

if so, you can't do this in the same cell without using some VBA code.

please clarify if this is what you want


"ringo tan" <ringo> wrote in message
> How can I restraint the user from entering odd numbers in the cell? But I
need the outcome to be in multiples of 5?
> Thanks.
> Ringo Tan

JulieD1 (2295)
8/5/2004 6:23:15 AM

Similar Artilces:

MAPISP32 has taken over - uses almost all of the CPU (97 to 99%). Can't send or receive email. Restarting and not launching OUTLOOK gives me back my computer, but no email. Using OUTLOOK 97 on an XP machine. Have checked the postings, but found nothing about this problem (I get no error messages). Also tried renaming MAPISP32.dll and then reloading OUTLOOK, no improvement. Will check back later and thanks! ...

inserting a hard return with replace in a cell
Is there a way to insert a hard return or line break using the replace function in a cell? Thanks brent Use char(10) in your =replace() function. (and remember to turn wraptext on) Brent wrote: > > Is there a way to insert a hard return or line break using the replace > function in a cell? > Thanks > brent -- Dave Peterson Thanks! "Dave Peterson" <> wrote in message > Use char(10) in your =replace() function. > > (and remember to turn wraptext on) > > > > Brent...

How to count cells
Hi, I want to put a summary of Job orders in a table. (Excel (Zip) file attached) I am having a problem of total. I want to make a total of only approved job orders not the all job orders. Also if the column contains hot / cold type, I need red and bold for hot job order of complete row. Also I want to be counted by job type and entity. For example, in the total approved 7 permits, CONST entity 3 cold jobs and 1 hot job. how can i display in the abstract table. Can anyone help me please. +-------------------------------------------------------------------+ |Filename: ...

Beginning Inventory Balance #2
I am trying to duplicate the Average Inventory, according to KB856731, the formula is; (Beginning Inventory Balance + Summary of each months ending on hand Inventory + Current On Hand Inventory) / (number of months elapsed in the current year +2) Does anyone know what the formula is for the Beginning Inventory Balance? -- Anthony ...

Macro Security #2
Hi, I am trying to set the Macro security level to Low in Access 2003 but cannot find the option in the Tools\Macro menu. It is not even grayed out. The install is a complete install of Office 2003 and I tried a Detect and Repair without luck. Any suggestions? Thank you. Maybe a post in one of the Access newsgroups would get you an answer quicker. Clementius wrote: > > Hi, > I am trying to set the Macro security level to Low in Access 2003 but cannot > find the option in the Tools\Macro menu. It is not even grayed out. The > install is a complete install of Office 2003 ...

Removing characters from cell
Hope someone can help me with this one.... I have text in column "A" a list of parts like the following..... ...nhg1234 ..nhg1235.54 nhg3456 ....nhg1253.7 Is thier anyway of removing the dots in front the part number? I dont want the points moving after the the main part of the number. Please help TiA mag()() Do you part numbers all start with nhg? If yes, you could select column A edit|Replace what: .n with: n replace all And keep hitting the replace all button until all are fixed. "Mag()()" wrote: > > Hope someone can help me with this one.... >...

Spaces issue not fixed in 12.2
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel The previous threads have all been closed and none of them seem to have an answer. For anyone else struggling with Office not working with Spaces (and therefore having to disable Spaces to use Office 2008), here is the reply I just received from MS Office for Mac Tech Support: "The Spaces issue is still being worked on, please watch for future OS and Office updates" My guess is that as it has not been fixed in two service packs it is unlikely to change until Office 2012 (or whatever the next major release wil...

Unpopulated Cell Address
Hi I'm a newbie and was wondering can anyone help with my query. I am trying to find out how to print a spreadsheet with just the data that is contained in certain cell address's. Where a cell address is not populated I want the whole row to not appear in a printed copy. Any ideas? Try this idea Sub hiderowsandprint() With Range("a1:a21") .SpecialCells(xlCellTypeBlanks).Rows.Hidden = True .Resize(21, 8).PrintPreview .Rows.Hidden = False End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Mick Smith" <notsa...

change the formula by changing contents of cell
I have a table that ranks a list of players by their statistics, for example, when I change cell B2 to "walks", my formula (large) lists the player with the most walks in cell B4, 2nd most in B5, 3rd in B6... that works well because more walks are good. But, when I change cell B2 to "strikeouts" I want it to list the player with the fewest number of strikeouts in B4, next fewest in B5 and so on. Is there a way to change the "large" function to the "small" function within the formula? I have set up a helper cell (C2) that changes fro...

Combo Box from data in another tab
Is it possible to create a combo box from data that's in another tab? I have a combo box that's from data in hidden columns. But now I need to move all that hidden data to another tab in the same file. But when I go into properties and go to the ListFillRange option, it won't let me enter another tab name. -- Message posted via I have called data from another tab with a combo box by doing th following: First - name the range of the data on the other tab. This can be don with the shortcut on the toolbar (l...

Preventing Column Cell Values from Printing
I have a summary worksheet with several columns of information. There is one particular column (E25:F39) where I don't always want to print the values. Is there some simple way to toggle this column's values on and off just before hitting the print command? I was thinking of using an adjacent tick box outside the print area, which when ticked would print the column values, and vice-versa. I'm looking for a formula that would examine the tick box, and if the tick (letter 'a' in Marlett font) is not present (i.e. cell value = ""), then the values in column (E25:...

Conditional Formatting #2
Hello, I have a spreadsheet that has about 1200 rows many of which need to have conditional formatting. I had a template set of rows that I was transferring the conditional formatting from and it worked for 20 or so rows. Then in attempting to both copy/paste and drag formatting even further I noticed that the conditional formatting was not being transfered to the target cells. I have even tried doing one row of formatting at a time but it seems like it will not allow any more conditional formatting. The spreadsheet is a good size (about 2MB). I recieve no error messages about this. It simpl...

Adding a new data series to an existing chart
I have a stacked bar chart that shows monthly sales by customer type. I want to add an additional data series to show the monthly forecast, to be displayed as a point or line against the existing stacked-bar data. Any ideas? Select the data you want to plot. Drag onto the chart. XL will pop up with a dialog box asking for some information. Provide it and you will be all set. -- Regards, Tushar Mehta MS MVP Excel 2000-2004 Excel, PowerPoint, and VBA tutorials and add-ins Custom Productivity Solutions leveraging MS Office In article <1418C2D7-4DF6-4945-A08A...

How do I change the value in cell based on a future date
I would like certain cells to be cleared after a certain date one way: Put this in the ThisWorkbook code module (Right-click the workbook title bar and choose View Code): Public Sub Workbook_Open() If Date > #12/15/2004# Then _ Sheets("Sheet1").Range("A1,B2,J10,K39").ClearContents End Sub Note that this won't work if the user opens your workbook with macros disabled. Note also that there's no way to prevent a user from accessing your data - i.e., they can set their clock back and open the file. You can make things more difficu...

IF COUNTIF & COUNTA on Filtered Visible Cells #7
Hi Frank, Hope this will help. Correction to Last Posting: My Helper Column "U" increments one Row at a time and says: Check from Row above Current Row back to beginning of my "V" Range: i the Room was used previously give me the Last (MAX) time it was used b returning the relevant Row Number of the (text based) Group Nam (Executive, Trainees, Manager, Graphics, etc.) that used the Room Last The Helper Column "U" then passes this data to the Formula in Colum "T" - it checks for the criteria within the specified Range and doe the COUNT calculation us...

Excel 2000
Hi! *First off:* I have created a spreadsheet that has a dynamic range an data validation. *Next:* i know i can add options to the 'dynamic range' and my dro down menu will add the option to its list. *Problem:* How can I make it so the user can add data into th validated data list/drop down menu rather than having the user addin it into the dynamic range? *Example:*this drop down menu indicates how often a system is updated weekly, monthly, etc. etc... i don't want to restrict it, so i want t make it so they can indicate their own time on how often those update occur. TIA -...

Printing 2 worksheets to a 2 sided document
Is there any way to print - front to back - 2 seperate worksheets? Turn the paper over and print the second worksheet???? Maybe you can create a worksheet with a picture of both ranges on it: Insert a new worksheet Edit|copy the first range shift-Edit|Paste Picture Link (on that new worksheet) Back for the second range and shift-edit|Paste picture link (right near your first pasted picture link). (insert a nice page break, too) And by pasting a link, you can change the original range and your picture will update right away. (Keep that worksheet as long as you want and print from there???)...

Export #2
Hi, How to export only the perticular group users. Regards Mustafa What sort of information do you need? "Mohammed Mustafa" <> wrote in message news:OwKSDO$yGHA.3568@TK2MSFTNGP03.phx.gbl... > Hi, > > How to export only the perticular group users. > > Regards > Mustafa > See "IMI GAL Exporter" - "Mohammed Mustafa" <> wrote in message news:OwKSDO$yGHA.3568@TK2MSFTNGP03.phx.gbl... > Hi, > > How ...

Data within a cell
If names are in cells as "last name, first name, middle initial" can a macro be run to change the order to "first name, middle initial, last name"? A formula will do Assuming you have spaces between commas and names, =MID(A2,FIND(",",A2)+2,FIND(",",A2,FIND(",",A2)))&RIGHT(A2,FIND(",",A2,FIND(",",A2)))&", "&LEFT(A2,FIND(",",A2)-1) Then just copy down. If you want this more permanent, copy the cells with formulas, right click, paste special. -- Best Regards, Luke M *Remember to click &q...

solver and macros #2
Before you use the solver within a macro, you must establish a reference to the Solver add-in. With a Visual Basic module active, click References on the Tools menu, and then select the Solver.xla check box under Available References. If Solver.xla doesn't appear under Available References, click Browse and open Solver.xla in the \Office\Library subfolder. Cheers Nick ...

Transfering a changing cell value progressively through a workbook
I am creating a payment application form in excel 2007 using windows 7. Each worksheet represents one months invoice. Say I have a formula in "sheet 1/ cell Q7" that sums the total billed to date for a particular budget line item. This value will be transfered to a the next months payment application "sheet 2/ cell K7" this becomes the total amount of previous applications, then "sheet 2/ Q7 is =sum(K7,M7,O7) this value is transfered to "sheet 3/ K7, and so on and so on, until the completion of the job. How do I acomplish this. -- Thanks Mike ...

Data entered from list automatically enters number in another cell
I am using Excel 2007 and here is an simplified example of what I need. Items Price Potatoes 4.35 Apples 5.55 Oranges 7.95 Onions 4.55 Carrots 3.75 Items Column is List for Valid entries in table below Prices are the numbers I want entered when I enter the Item If I enter Oranges from Dropdown List in A16 , I want Excel to automatically enter the number 7.95 3 columns over in D16,or if it is quite a bit easier, just 1 column over in cell B16 Example of table I want: My Entries: Excel Enters ...

How do I make the x axis data the y axis data?
My graph automatically makes certain data the y axis and other data the x axis. I need to just reverse it for the graph I am looking for. Any suggestions? SLG, One option is to reverse the positions of the data on the spreadsheet. Assuming an XY chart, you would change a setup like this: x y 5 4 4 2 5 3 7 4 6 5 to this: y x 4 5 2 4 3 5 4 7 5 6 If this doesn't work, can you post back to let us know what type of chart and data you're using? ---- Regards, John Mansfield "SLG" wrote: > My graph automatically makes certain data the y axis ...

Closing registers #2
1. Is there any way too remove the closing figure when the registers are closed... I don't want my cashiers to see the closing amounts. 2. Can a group of registers be closed out on a selected machine, e.g Reg 1, reg 2, reg 3, reg 4 are all closed on reg 1. Thanks If you don't want cashiers to see closing amounts, lock them out of doing x/z reports and viewing the journal - apply this setting in SO manager - database - cashiers - properties of each cashier - uncheck these boxes. On question 2, out of the box, RMS does not allow that - each register has its own batch that must ...

converting plain data to table format
It has been a while since I used excel but I am almost sure there is a way to convert just plain rows and columns of data into a table format. This way I believe that adding rows to this block of data is as simple as tabbing after the last cell and it should just drop down to the next row and insert one??? correct?? Excel should recognize a contiguous range as a table, or list. Include headings in the first row, and leave at least one blank row and column between the table and other items on the worksheet. You can use the built-in data form to add records to the table (Data>Form). Or,...