Validate A Cell To Only Allow Items In List1 Or List2 Or A Numeric Value
Mar 31, 2009
I have 2 lists and I want to validate a cell to only allow items in List1 or list2 or A numeric value.
List1
D1
D2
D3
..
D10
..
Dxx
List2
Hol
Off
Sick
These lists may changein length and number of items
the second part, is can you valudate based on other values in a column, basically I have a list of names of available operatives, and I want to make sure each name can only be entered once in a column! Would be even better if I could get a dropdown which showed the remaining choices!
View 3 Replies
ADVERTISEMENT
May 13, 2014
I need to determine if excel cell contains any character except number If it contains any character then place 1 in adjacent cell for example, otherwise 0
Column                A                    B                          Â
150 000Â Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
150000Â Â Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
150,000Â Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
150.000Â Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
150000 kzt     1                          Â
150000kzt       1
View 5 Replies
View Related
May 16, 2013
I've got a userform for pricing items and am having an issue when changing margin. I want to validate the user enters in .22 or 22%. The code places the decimal value in a worksheet just fine and runs back end calculations. I want to make sure no one fat-fingers .12b by accident so I came up with the following code. It seems to run fine, but if I tab over a couple of textbox (there are 4 Margin textboxes) it trips the coding for that textbox even if there was no change to the value.
Code:
Private Sub txtPDLaborMargin_AfterUpdate()
If IsNumeric(txtPDLaborMargin.Text) Then
Range("LaborMargin") = txtPDLaborMargin
[Code].....
how to validate the value is numeric
View 2 Replies
View Related
Mar 19, 2008
I would like to " Validate Data In A Vertical Column To Not Allow Non Consecutive Numbers Less Than 100"
756415
10
456132
7
456123
12
The above is a valid list. Below would be an invalid list:
756415
10
13
456132
7
7
456123
12
13
This part of a larger scope, but I have a macro that will crash if the data entered is entered by way of the invalid list, it works perfectly with the valid list. I am limited to one column user's will input the data via a Barcode scanner that after it recieves input it enter's a "Hard" return. This is a warehouse pickticket program, user's scan their ticket id's (numbers greater than 100000) and then the number of lines on the ticket (usually not greater than 15)
View 2 Replies
View Related
Mar 28, 2014
UREC Bar Event Sheet.xlsx
I am trying to correspond prices with products from the validate function in multiple columns. I want to select the product and have the price for the product populate in the 'cost' column. I have the list of products with corresponding prices on sheet 2 named 'products'. I have attempted to write a formula in cell G11 on the Event Sheet, but was unsuccessful. Please direct my path.
View 3 Replies
View Related
Dec 20, 2007
Can anyone help me to get a validation to check that what is entered in a cell, lets say A1,
has to be number between 100000 and 899999 (this I already have) OR a value/text from I100000 to I899999. So it only acceots a vlaue betwwen 100000 and 899999 and it is also ok to have the letter I at the begining.
View 9 Replies
View Related
Oct 22, 2009
I am trying to do is extract the volume size of products in 'ml'
from 10k plus products from a description field cell.
this description field could also contain the weight of the product in grams
so I cannot just do a search for a numeric string ,
it has to be associated with the milli-litres statement .
is is possible to do a sort of ' *ml ' search and then select and copy to another cell ???
View 9 Replies
View Related
Apr 7, 2009
I would like to simply validate the value entered to a cell against cells of a number of columns. I’d like it to return a vlue to be able to report it in another cell as below. (had to cmma separate the columns, can't get them aligned in this editor)
A, B, C, D, Q, R
1, b, 1, 2, 1, existing
or
A, B, C, D, Q, R
1, b, 1, 2, 5, not existing
So the user enters a value in cell Q to check against the other columns and return a value to R. Additionally, the cells to be validated (A - D) may contain more than one character, i.e. cell A could = 1b2.
View 4 Replies
View Related
Feb 1, 2010
The code below automatically displays the validation when I select the cell. But I would like the cell validation only display when the cell is empty. If the cell has data in it, then do nothing, but if the cell is empty, then display the validation.
If Not (Nothing Is Application.Intersect(Target, Range("B9"))) Then
SendKeys "%{down}"
End If
View 9 Replies
View Related
Aug 10, 2009
I've been struggling with this for a little while today and quite can't seem to figure it out. I have a range --- A6:A28 --- where a user will enter a percentage. The total should add up to a 100%, which is in cell A30.
My understanding is that a need a worksheet_calculate function to tell users to revise their entries if the results in a cell A30 are either less than or more than 100%. This is the simple, non-working procedure I have now:
View 4 Replies
View Related
Jun 25, 2013
If I have a cell "C8" default value is "choose" and it is a drop list of other values. I want to validate if "C8" is populated with a value other than "choose" that the user populates the SOME of other cells in the row like D8 or F8? I would like to alert something that the cell needs to be populated.
View 1 Replies
View Related
Jun 21, 2008
How can I validate a cell to get only text and undescore?
Not any other special characters, like space, @#$%^&*()+!~
View 9 Replies
View Related
Nov 2, 2009
Cell C10 contains 2 possible values, "CLIENTS" or "INDIRECT"
These are also the names of 2 dynamic named ranges
I am trying to use a COUNTIF to validate a subsequent cell, checking if the contents appear in the original list.
=COUNTIF(INDIRECT(C10),D10)
Checks to see if the value entered into D10 is within the dynamic named range, the name of which is entered into cell C10
Gives me a #Ref error
View 9 Replies
View Related
Aug 14, 2006
I have a sheet which in a certain cell (H4) must contain either an "I", "J", "R" or "S" in upper case.
The user fills in the sheet, and then a macro runs that does lots of things. I have been asked to put a bit of code at the start of the macros to validate the contents of cell H4.
However it doesnt seem to work, particularly the isempty statement
Sub SiteVal()
'
' SiteVal Macro
'
If ("H4") = "S" Then
Else
If ("H4") = "I" Then
Else
If ("H4") = "R" Then
Else
If ("H4") = "J" Then
Else
If IsEmpty("H4") = True Then
MsgBox "Site code must be input"
Exit Sub
Else
End If
End If
End If
End If
End If
'
End Sub
View 4 Replies
View Related
Apr 18, 2007
I would like to use VBA to validate the data in an Excel worksheet against either a named range or an array of valid data.
The valid data comes from an accounting system. It is a list of customer numbers. I am using ODBC and SQL to get the data into an array and then into a named range. This is working fine.
I want to validate the customer number column in my source Excel worksheet against either the array or the named range.
There are a variable number of lines in the source data.
I have a VBA procedure which starts at cell A2 and then works down column A until it reaches a blank cell.
I would like to use a VBA formulae which does something like:
IF active cell offset (0,3).Value in (named range or array)
Then set cell colour for active cell offset (0,3) = Green
Else set cell colour for active cell offset (0,3) = Red
End If
Or something like that.
Can this be done using VBA? Or do I need to create a new column in my source data and use VBA to place a VLOOKUP based on a named range into this new column?
View 9 Replies
View Related
Aug 22, 2007
I have a column which is formatted to custom [hh]:mm but could also have the following text entry "NWD", which is either the hours a member of staff works or Non Working Day.
I tried using a data validation list with **:** and NWD, but it recognises the **:** as exactly that.
View 6 Replies
View Related
May 21, 2014
Userform that people use. I need validating the users input into the form.
1. Need to validate that A1 only has 3 letters (Alpha)
2. Need to validate that A2 is not left blank
3. Need to validate A3 has only 10 digits
My state is that when the user hits the submit button in the user form the macro checks for these validations above and if everything is ok emails the sheet. However if the user havent inputed the data correctly a msg box will pop to let them know what is wrong. If thats the case i do not want the sheet emailed. below is the email code i'm using
HTML Code:Â
Sub SendMail()
' Copy the sheet(1)
ThisWorkbook.Sheets(1).Copy
' Send Email
.SendMail Recipients:=Array("email.address@email.com"), Subject:="Test" & Format(Date, "dd/mmm/yy")
.Close SaveChanges:=False
End With
End Sub
View 2 Replies
View Related
Apr 28, 2009
I have been trying to figure out how to use a named range (on another sheet) to validate the user entered data in a specific column. All my attempts at utilizing worksheet_change event have ended in errors. I've created a simple workbook that shows what I'm trying to do and have attached it to this message. The 'Sample (Data)' worksheet has the data table that would be completed by the user. On the 'Validation' worksheet I have named the range to be used to validate the data as 'rngVal'. So when something is entered under the 'Expense Type' heading, the code would verify that the value entered was contained in the 'myVal' validation list. If it was not present in the list, a msgbox would instruct you to try again. After stealing bits of knowledge and code from many of the posts on this site I cobbled together the following (which results in an error at the 'set rngFind' line):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngFind As Range
'Set Target = Cells(ActiveCell.Row, ActiveCell.Column)
Set rngFind = Range("rngVal"). Find(Target.Value)
'If data in column C changes, do the following
If Target.Column = 3 Then
'If the value is found on the validation list, do nothing.
If rngFind Is Nothing Then
Else
MsgBox "You must enter one of the following in this cell:"
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If
Else
End If
End Sub
View 6 Replies
View Related
May 30, 2008
excal VBA programming.I have attached the file name "help" for your easy explanation purpose.
1. Is it possible to hide sheet nos. 1,2,3,4 & unhide the sheet as wished by me by puting the value (1or 2 or 3 or 4) in B3 cell.
2.There are per day production rate in E18 to E22 cell. Now whenever I will give value in H18 or H19 or H20 or H21 or H22, it will check whether the value is same with the respective E 18 or E19 or E20 or E21 or E22 cell. If both the values are not equal then give a message box "WARNING!!! YOUR VALUE IS NOT SAME". Can it be possible by creating VBA programming.
View 5 Replies
View Related
Sep 20, 2007
i have a data validation problem is there a way i can use =INDIRECT(DEC!'Ai11>DEC!'AJ11) is this correct? when the cell in dec AI11 is greater than AJ11 i need a validation stop to take place
View 9 Replies
View Related
Jun 4, 2014
I have a column of several thousand entries listed as numeric with a scientific symbol eg.
1.4mSv
19.53mSv/1mSv (some have a mix and or alpha/numeric range)
I want to convert them to the numeric value only. I'm extracting to a chart which is not recognising the alpha and throwing the data out. I tried find and replace, trying various options within the 'replace format' tab with no joy.
View 6 Replies
View Related
Sep 1, 2008
I have a few thousand products codes (i.e ABCD123BLA08 or SHU267BLA) They are non standardised in length or structure. I wish to pull out everything upto the end of the third numeric digit.( ie ABCD123 and SHU267) I cannot use left as they are all different lengths. Ideally i would serach for a non numeric char after the number then use left up to that point. Search can't do this, FIND can't do this.
Summary
ABCD123BLA08 would be ABCD123
SHU246BLU would be SHU246
I147ORT08-12 would be I147
View 9 Replies
View Related
Oct 14, 2009
I have an Excel workbook which contains data entry fields, which have different types of data validation rules - like Lists, Date, Whole Number.
I do not want end users to remove these data validations as well as the formatting of these cells by doing copy/paste. So, I have implemented techniques mentioned in the following post, and elsewhere - to override the paste functionality and implement PasteSpecial values automatically.
[url]
To keep it simple, I'm only supporting pasting a single cell at a time.
Now my problem is this:
Doing the PasteSpecial values programmatically doesn't prevent the user from pasting values in the cell that violate the data validation rules. So, I can paste a string into a cell having data validation as Whole Number, or a invalid string into a cell having data validation as List.
The following post just suggests disabling paste whenever data validation is present:
[url]
But I would like to allow the paste operation if the value being pasted is a valid value for the cell's data validation.
View 9 Replies
View Related
Jan 12, 2010
I have a column that has cells with values as
19352510
C084111X AA
24253081
A001290U AA
19599291
48413321
I want to write an If Statement is a column next to each entry that denotes two options either Broker or Agent. Where the code is say 19352510 then Broker and where it is say C084111X AA then Agent.
Broker codes will never contain a letter. The Agent code will always start and finish with a letter.
View 9 Replies
View Related
Aug 14, 2012
I have a input box that prompts a user to enter a date of a new month - it has to be the 1st of a new month. I have validation that it is a date that has been entered but then i want to validate the date entered is a month ahead of a date in a cell range on a sheet.
It is a monthly reset so it has to roll on from the previous month.
Here is what i have currently but it isn't working.
Code:
' Get user to input the first day of the new month to populate all dates with
dNewMonth = InputBox(Prompt:="Enter first Day of the new Month. Must be the 1st of the Month e.g. 01/10/2012", _
Title:="Enter Date")
' Validates the entered date is a valid date
If (IsDate(dNewMonth) = False) Then
[Code]...
View 1 Replies
View Related
Dec 3, 2009
cell A1 is equals to B1 because the cell C1 contains the number 1.
Now if i change the value of the C1 to 2 how can i make the A1 shows the value of the B2 ?
Just like. [A1]=B(C1-content)
View 3 Replies
View Related
Mar 13, 2008
I have a cell (B2) I would like to apply multiple data validations to.
I know I need to use the custom formula option but don't know how to write the formula.
I don't even know if it is possible, but here is what I'm after
I need to make sure the cell is 4 digits long
I need to make sure the cell starts with a zero (Because the cell starts with a zero I have it as a text cell)
I need to make sure the 2nd number is not 0 if A2 begins with 5 (A2 is also a text cell).
View 6 Replies
View Related
Jul 23, 2014
I have a sheet (example attached) I need a formula to recognise only the numeric values either by automatically deleting the words or by entering the numbers in another corresponding sheet, either would do.
View 5 Replies
View Related
Feb 29, 2012
I am trying to delete a row if the cell value of column A is a number and not a name.
What i have been using to remove things is this.
Code:
Last = Cells(Rows.Count, "A").End(xlUp).Row
For G = Last To 1 Step -1
If (Cells(G, "A").Value) = "" Then
[Code].....
View 2 Replies
View Related
May 7, 2013
I have the following issue:
=AR85*36*75*6.29
The above formula does not return any value and it simply appears as it is keyed. Cell AR85 has 5. I know it is something to with this cell, but I cannot fix it. In fact the whole Column AR has these values downloaded. I tried "text to columns" but it still doesn't work.
View 9 Replies
View Related
