to validate a cell for the following customer format, but cant get it to work:
XX00000
Where XX is any combination of letters (upper or lowercase) and
00000 is a combination of any 5 numbers.
It must always be in this format, with this number of digits in total (2 letters followed by 5 numbers)
Is there a way to have a data validation drop down menu but at the same time allowing users to enter free text as well.
View 3 Replies View RelatedIs it possible to force a cell entry (using Data Validation) to either a whole number or a value? In other words, I would like the data in a cell to be either a whole number between 10,000 and 20,000 or "N/A". I can do it with VBA, but Data Validation (if it's possible) would be better.
View 2 Replies View RelatedI am trying to setup a number of cells with a validation list allowing "Yes" or "". I would like the cell value to equal 1 when "Yes" is selected. Is this possible without VBA?
The reason for this is because I want to set up conditional formatting with the tick, cross, exclamation mark icon set (With a tick appearing for "Yes"), but this conditional formatting only works with numerical values.
how can I set a cell so that you can only type in 1 , 2 , 3, 4, 5
I know I can use the validation but people can just copy a cell and paste it over.
The numbers will be both negative and positive just need to catch characters.
So:
2500 is okay
-15 is okay
is not okay.
I currently have cell B3 in a worksheet - able to except 2 data entries.
The user should enter either OPQ or BOT followed by numbers. How do i ensure that they only enter OPQ (O - Letter Not Number) followed by numbers, or BOT (O - Letter Not Number) followed by numbers.
i.e. OPQ87654321 NOT 0PQ87654321 or BOT12345678 NOT B0T12345678. The information has to be entered manually and not via drop down list as each number is unique to the OPQ or BOT.
I want to create data validation so that the first 2 digits=PS and the next 4 digits will be numbers. I have tried a few things but I cant get anything to work?
View 8 Replies View RelatedI am looking for a solution for my problem: I work for a bus company, I need a spreadsheet where I will record the bus numbers into a sheet but I only want to be able to add the number once ie. error if duplicate bus entered. Also the numbers will change from time to time, old buses sold when new ones arrive. I have the following code that someone helped me with wich works fine provided you don't have to change the numbers.
=And(Or(And(E42>=121,E42<=125),And(E42>=149,E42<=156),And(E42>=320,E42<=363),And(E42>=700,E42<=799),And(E42>=800,E42<=991)), COUNTIF($B$3:$E$46,E42)<2)
I want to use Data Validation on a cell (H1) to only allow the value entered to be values between values in E1 and D1. E1 is a negative number.I tried naming E1 & D1 as a range (valid) and then did custom validation formula is =valid, but that allows me to put in anything.
View 3 Replies View RelatedHow can I require 2 numbers before a decimal in data validation? I have a formula to require at least to characters after.
I'd like to require the user to enter at least ##.##
I am looking for a way to define a 'Data Validated' Cell to hold a List of Dynamic Sequential Numbers - from 1 to the Value of cell A4. For example:
If cell A4 has a value of 5 the 'Data Validated' List will be 1,2,3,4,5
I have tried to 'name' the Array-Formula {=ROW(INDIRECT("1:"&A4))} and put the 'name' as the List source but without success.
I have created a template using data validations. I have a drop down in column O, which you can choose from Credit or Charge. In column V, I want:
- If selected Credit in column O, only negative numbers allowed in column V
- If selected Charge in column O, only positive numbers allowed in column V
I am using Excel 07 with Vista, and creating a spreadsheet in which users will be inputting a record into every row.
There is a row that requires a number to be entered, where some of the numbers will begin with 0 or 00 or even 0000. I want to make sure no-one incorrectly enters a letter, however with data validation set to whole numbers, it formats the cell to change an entry of "005667" into just "5667". There is no set length for the field.
The only way to have "005667" is the change the column to text, but then obviously that defeats the whole purpose!
The format of the text in which I need to extract numbers is as follows:
23411268 - 23411270
Need to extract the following:
23411268
23411269
23411270
These numbers have to be listed in three seperate rows.
I have a database of names and titles (acutally: #, last name, full name, title, meeting) and at this point I have a column using the following code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A5:A355")) Is Nothing Then
Range("A5:A355").Cells.Clear
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
Else
Target = vbNullString
End If
End If
End Sub
everything works like I want, except when I use the auto filter, the "a" won't be cleared from cells that are hidden. I need to make sure only one "a" is populated in this column as that's what I'm using with the vlookup function to key the data for my Character Generator.
Maybe I'm not using the best code, I'm new to this and just cobbled together code from a few different places.
Some quick background to what I'm doing, but you may not need it to help. I'm trying to use Excel to populate some cells that will tell our Chyron Character Generator (for those that don't know what that is, it's the device that puts graphics on screen during news programs, etc...) what to put up for lower third titles (and more) for our meetings we cover.
Is it possible to assign text using data validation?
Right now, the three cells I'm using for data validation contain text, but return a number to the linked cell.
I am having two problems ....
1) I have a field for which the user MUST enter text information. It can't be left empty. I set validation on the cell ... Text Length must be greater than 0. It still allows the user to hit enter without entering information without an error message and a retry.
2) In another field, the user has the option to enter (text) info or not. It can be left blank. The value in this cell, empty or other, is copied to another cell in another worksheet. The problem is ... the destination cell gets a value of 0 when the user field is left blank. The value of 0 in the destination cell can't be there ... I need that cell empty.
I have created an Excel spreadsheet in 2007. When a friend opens the workbook in Excel 2013, It does not let him add data to the cells. ( just one column which is the "date" column) The worksheet is not locked or protected.
When I right-click on the column and goto format cells, protection, the box is ticked, but the note says this doesn't take effect unless the sheet is protected witch it isnt.
i need some formula to data validation with this criteria :
allow only text "DROP" and "check" ...
Can data validation be used to enter a text message if no numeric value is entered in the cell?
I want to force the cell to have to be populated with a number and if not show "--".
I'm having difficulty allowing my macro to run in a more global environment with varying data-set lengths.
For example:
Though the following "copy" scenario works for me when my data set is contained within cells B2:B35:
Range("A2:A35").Select
Selection.Copy
It does not allow me to work with a set of data that extends beyond this--say to B50 or B60. Is there any way to allow this command, and others, to be a bit more flexible with my data set lengths?
Is it possible to validate a string with both numbers and text with a formula using data validation? (I don't want to use a User Defined Function)
in Column C, Starting in cell C2 I have a 12 character string of six numbers, four letters, and two numbers. like this:
280838ZNOV11
is it possible to use a Custom Data Validation formula to ensure the user follows this format?
I'm using Data Validation for various fields in a form and I'm trying to make it user friendly by adding Error Alert conditions and text explaining why the error has occurred. I also want the form to be translatable into other languages.
For some VBA message boxes that get displayed I can put the English text in some hidden cells and that can be translated easily by someone without access to the code. But I cannot figure out how to make the Data Validation Error Alert messages point to a cell so that they are also translatable without forcing the translator to open each Data Validation dialog.
Can anyone provide a formula to be used in data validation that will control text length (6 digits) and restrict duplicate entries. The best formula will prevent anything other that 6 digits, but question the user regarding a duplicate entry.
For example: if the user enters 123456 no problem, but if 12345 is entered, Excel validation would not allow. If the user enters 123456 again, Excel's validation window would allow but the window will pop-up and ask to confirm.
Can I limit the text length in a cell that contains a formula? You may say "Limit the text length in the input cell". That can't be done because the formula in the resultant cell takes text from two other cells in addition to the input cell. I need to limit the overall text length to, let say, 50 characters.
View 7 Replies View RelatedI am quite inexperienced with Excel and I am struggling to understand how should I set up a formula, within the data validation function to achieve the following:
I need to restrict a certain set of cells, for people to be able to input only the following values: "1", "0.5", "A", "H", "0.5A" (or 0.5a), "0.5H" (or 0.5h)
And I would need the data validation tool not to include blank cells.
I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
View 2 Replies View RelatedVBA code which can distinguish whether a cell has been edited by:
selecting an option from a data validation list (DVL)
or
manually editing the text in a cell?
The ""Show error alert after invalid data is entered"" option is unticked to allow both selection from the DVL as well as free format text entries.
The problem arises when using the code below which makes an edited cell within a range display the new value as well as the prior value(s). This works well when using the DVL but not when editing the cell.
Is there any code which can distinguish between the 2?
[Code] ....
I am trying to set up a data validation to ensure that when a user enters a number in column A that matches a previously entered number in column A, the user is only allowed to enter the same date for the number it matches in column B.
For example, if:
Row 3 column A = 1320
and
Row 3 column B = 16-Dec-07
and
Row 15 column A = 1320
then:
Row 15 column B must be 16-Dec-07