Validate A Cell To Only Allow Items In List1 Or List2 Or A Numeric Value
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Validate Cells For Numeric Range Of Consecutive Numbers
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 Replies!
View Related
Validate For Numeric Range & Alphanumeric Range
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 Replies!
View Related
Extracting A Numeric A Specific Numeric String From A Description Cell
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 Replies!
View Related
Validate Empty Cell
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 Replies!
View Related
Validate Cell Values?
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 Replies!
View Related
COUNTIF To Validate A Subsequent Cell
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 Replies!
View Related
Validate Cell Value Against Range Or Array
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 Replies!
View Related
Validate Result In A Calculated Cell
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 Replies!
View Related
Validate Cell Not Empty Or Certain Letters
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 Replies!
View Related
Validate Cell For Time & Text Only
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 Replies!
View Related
Validate Cell Entries To Named Range List
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 Replies!
View Related
Hide/Show Sheets Based On Cell Values & Validate Entry
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 Replies!
View Related
Finding A Non Numeric (or Numeric) Character In A Field
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 Replies!
View Related
How To Validate If Data In A Cell Conforms To Data Validation Rules In The Cell
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 Replies!
View Related
If Statement Numeric Or Alpha Numeric
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 Replies!
View Related
Finding First Non-numeric Cell
Sub Test2() 'From the top down If IsEmpty(Range("A1")) Then Range("A1").Select Else Range("A1").End(xlDown).Offset(1, 0).Select End If End Sub I would like to change this to find the first non-numeric cell (technically the column i'm searching is dates, but i assume those are seen as being numerical). I tried to change this line: If IsEmpty(Range("A1")) Then TO If IsNumeric(Range("A1")) Then But as many of you know it didn't work (i also tried variations of IsNumeric(Range("A1")).Value=True
View Replies!
View Related
Extract Numeric Value From Cell
I have compiled data from accross my branches, where in a 14 digit voucher number has been fed into the system along with data. The problem is that there is no unique format as this voucher number is fed in free format area. . i need to extract this consignment number from the cell value. for example, A10 Contains "voucher No 12345678901234 paid" A11 Contains "spl cons 15454563218921 thru person" A12 Contains "incurred for recurring exp 45678932145826" . writing a macro to extract this 14 digit voucher number. .
View Replies!
View Related
Validate Cell For Text Length & Characters In Text
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 Replies!
View Related
Numeric Format Based On Cell Value
I am stuck on this one. In the attachment I have values pulling into N3:N7 based on a drop down choice. Some of the numbers are numeric (no decimals), others are currency (no decimals), others are percentages (2 decimals), etc. Is there a way to pull values I'm currently pulling into column N and have those values match the format of the source? I've tried converting it into TEXT but then I cannot place conditional formatting data bars on the values and have it work.
View Replies!
View Related
Reading Non Numeric Text In A Cell
SUM(IF(FREQUENCY(E10:E29,E10:E29)>0,1)). this is the formula I currently use to read employee numbers and it works when we just use the number i.e. 011004. When we use the full employee number with alpha characters it does not work i.e. ASMO011004. I have used helper cells to do similar, but am not wanting to do this way for simplicity reasons.
View Replies!
View Related
Picking Numeric Part Of A Cell
how to pick the numeric part out from a cell? e.g. range("a1").value= 384HK in range("b1"), i would like to have only 384, ignoring the HK any excel tools or VBA code can accomplish this? there is a bunch of data that comes like this and i just dont have time to retype only the number one by one
View Replies!
View Related
Converting A Numeric Cell To A Letter Grade
I, too, am trying to use excel to the fullest. My first issue is, I would like to convert the numberic grade in one cell to a letter grade in another cell. I am not really good with all the vocabulary, but would love to learn it, and can copy a formula pretty well!!!! Not so good with functions and macros but would love to learn. All help appreciated.
View Replies!
View Related
Format Specified Numeric Characters In Cell As Subscript
I have a long list of chemical formulas that I want to format (partially) as subscript. Basically what I need the macro to do is look at each character within a cell and check to see if it is numeric. If it is AND it follows a non-numeric character it should be formatted as subscript. Examples H2O the 2 should be subscript H2SO4 the 2 and the 4 should be subscript 2CCl4 only the 4 should be subscript CuSO4 - 5H2O
View Replies!
View Related
Return Value Based On Cell Being Within Numeric Range
I have a spreadsheet that has (amongst other things) 2 charts based on dynamic data with ranges that redraw using vb... The charts will draw envelopes that correspond to an aircraft's moment or c of g envelope - there will typically be 2 envelopes drawn, "normal" and "utility" or "aerobatic". Plotted on the charts are an aircraft's takeoff and landing weights and moment/C of G. I'd like to be able to use a formula or VB to determine which envelope takeoff/landing information falls in (or if it falls outside all envelopes). This data would then be used to generate an error message/possibly also in conditional formatting, etc.
View Replies!
View Related
Format Some Numeric Characters In Cell As Subscript
I thought I had this solved but an inconsistency has shown up. I have a long list of chemical formulas that I want to format (partially) as subscript. Basically what I need the macro to do is look at each character within a cell and check to see if it is numeric. If it is AND it follows a non-numeric character it should be formatted as subscript. Examples H2O the 2 should be subscript H2SO4 the 2 and the 4 should be subscript 2CCl4 only the 4 should be subscript
View Replies!
View Related
Separating Numeric And Alpha Cell Contents
I have a set of text strings that have some number of alpha characters followed by some number of numeric characters. I need to separate the data into two cells. Example in A1-A5 ABC123456 AB12345 A123456 AA12345 DEF123456 Desired results in B1 - C5 ABC 123456 AB 12345 A 123456 AA 12345 DEF 123456
View Replies!
View Related
Adding Cells Based Off A Set Numeric Value From Another Cell
I have an excel 2008 sheet and want to add cells in an odd way. Here is the best way I can describe it. I want a formula to add cells based off a number value in another cell. So it would be something like IF M3= 1 add C5 to SUM ; If not then go to next step IF M3= 2 add F5 to SUM ; If not then go to next step IF M3= 3 add I5 to SUM ; If not then go to next step IF N3= 1 add C5 to SUM ; If not then go to next step IF N3= 2 add F5 to SUM ; If not then go to next step IF N3= 3 add I5 to SUM ; If not then go to next step....................
View Replies!
View Related
Pass Number From TextBox To Cell In Numeric Format
I have uploaded a copy of the spreadsheet I am working on and have completed the user forms and coding for march the 1st only, so if you test, please use the options march and then the 1st! The problems i have is, when I enter a number into a text box and press next page, the numbers fill where i want them to but the cells do not recognise them as numbers, therefore conditional formatting doesnt work! Even if I change the cell properties to numbers, this does not remove the error! Second problem!! If a user forgetts to enter a value in a box, or they wish to edit just one value, and go back into the user form to change a figure, when they press next page, all the values in the column seem to disappear!
View Replies!
View Related
Sum 1 Or More Items In A Cell
I have a monthly calendar on each sheet for employees, attached is a copy of one of the sheets. I can have up to 2 combinations of text in a cell, i.e., 'V' or 'T' in this example, followed by a number, that can range from 1-12, i.e., V12T4. What I am trying to do is add up the total 'T' and 'V' time for the month for each employee. Hopefully between what I have written and my attached sheet, will explain what I am trying to do. I know how to write the =Sum(If for using only 1 item in a cell, but not 2.
View Replies!
View Related
Selecting Multiple Items From A List In One Cell.
Is it possible to restrict the values of cells in a particular column to entries defined in a list BUT to allow each cell in that column to display multiple items from the list (seperated by a comma for example). I've attached an example of what I would like to do - In Sheet "2010 Data" I want to be able to select multiple values in column F....(the values are defined within the list named "Platforms" on the worksheet called "Lookups"
View Replies!
View Related
COLOR Items, Text & Cell(s), Unresponsive
I should much appreciate your help on this strange new problem which is twofold: 1. Clicking either color item (for text or for cell) brings no change in designated cell(s) 2. When colors appear on Print Preview they do not on the Printout. It occurs on Dell Vista Ultimate SP2. Am on IE8; Ctrl Panel list also shows !E7. It does not occur on HP XP SP3 desktop although no discernable differences in spreadsheet. On laptop I am unable to use either COLOR icons -- for text or for cells. No trouble heretofore which makes it so frustrating. Ordinarily, following usual usage, all is well. For unknown reasons, now neither has any effect. Explored for cause/cure without finding anything more than instructions already well known. Did see note saying High Contrast might make colors ineffective, so searched out that. Found how to turn on/off, and easily turned off High Contrast. Made no difference.
View Replies!
View Related
How To Use One Cell To Match Pairs Of Cells For Missing Items
We use a program at work to check items out to individual people but sometimes an item is missed and it takes forever to manually look for who it is. In the attached example, (keep in mind there might be more than 100 people on this list...this is just two people), the first 2 items are a pair (radio and radio case) and the second 2 items are a pair (laptop and laptop case). Id like a way (condition formatting) to flag a person who has one item from the pair checked out and not the other. (Ive manually highlighted one person who only has one of the paired items...id like this to happen automatically) EDIT:: If the first person were missing the radio in this example, his name would be highlighted on the line with the matching pair...cell D1 (radio case) Other information: Radios will always be RFAMRC*** Radio cases will always be RFAMF**** Laptops will always be RFAMTD*** Laptop cases will always be RFAMTE***
View Replies!
View Related
Find Array Uniqe Items And Transpose 1 Each Into Cell
I have a col of dates that change, 9/15, 10/15, 11/05 and reside in col. I I then have a corresonding cell in row I136, M136, Q136, U136, Y136 and AC136. I want to find the starting at the earliest date starting in I36 , M136, Q136... So I136 would be updated to 9/15, M136 = 10/15, Q136 = 11/05, ... I am thinking a CSE type formula would be a possibility, but need assistance in this or in a piece of code..
View Replies!
View Related
Validate A URL
We have an internal web site that has files I need to download daily. The filenames have date strings in them. I've setup some formulas to make the url based on the NEXT dated file I need to download. And I don't have direct access to the drive the files are stored on, I can only get them through this web site. Right now, I have individual macros for each file I need. They'll follow the url and download the file if it's there, or return a message to me if it's not. But there are several different files. I have to run each macro one at a time, at different intervals during the day until they get downloaded. Is it possible to make a macro loop through all the URLs (I have them stored on a sheet, called "FileDownloader" in Range G2:G10) and check if the URL's are valid (without actually attempting to download the file). I can then make some kind of dashboard to tell me when the files are ready for download.
View Replies!
View Related
How To Validate A Formulae
I have a database on one sheet and a 2 count if formulae recording information on the next to be exact one formulae counts the number of monthly values and the other count yearly values. I want the sum of these formulaes to be equal or less than 25. and to show an error if the sum of these is mor than 25.
View Replies!
View Related
Validate The Data
I have a spreadshhet which has data by month, year, week and bi-weekly. I would like everything to be monthly. How can I put a formula which will look up the cell and see if it monthly it will the value of the cell beside it, if it is weekly it will take the cell value and multiply by 2 and so on.
View Replies!
View Related
Validate List - Restriction
I have a sheet that contains two values in seperate columns that I need to concatinate into one value for the drop down. If I use one value my code works but doesn't show all the values. If I use both values I get a 1004 - Application Defined or object defined error. I bolded the section that if I add I get the 1004 error. Is there some sort of restriction when creating the validate list for a drop down? My list is not reaching 65000 characters which is the string cut off. Dim intAdEventCol As Integer, intAdEventNmCol As Integer, intRow As Integer Dim intLastRow As Integer, intLastCol As Integer Dim strAdEvent As String, strAdEventNm As String Dim strAllAdEvent As String Do Until intRow > intLastRow strAdEvent = Worksheets(gstrcDataWorkSheet).Cells(intRow, intAdEventCol).Value strAdEventNm = Worksheets(gstrcDataWorkSheet).Cells(intRow, intAdEventNmCol).Value If intRow = 2 Then..........................
View Replies!
View Related
Validate InputBox Entry
validation up to the point that the user can click cancel and exit, also when nothing is entered a msgbox appears and for the 3rd inputbox a value greater than 0 must be entered. Sub Trajectory() 'Trajectory macro t0 = InputBox("Enter a value for the initial time(t0)") 'If t0 = "" Then MsgBox ("You must enter a value for t0!") 'Exit Sub tf = InputBox("Enter a value for the final time(tf)") 'If tf = "" Then MsgBox ("You must enter a value for tf!") 'Exit Sub Dt = InputBox("Enter a value for the time increment(Dt)") 'If Dt = "" Then MsgBox ("You must enter a value for Dt!") 'Exit Sub 'If Dt = 0 Then MsgBox ("You must have a valid increment") x0 = Val( Range("F4")) v0 = Val(Range("F5")) g = Val(Range("F6")) y0 = Val(Range("F7")) q0 = Val(Range(" F8")) Selection.Formula = FILL_TABLE End Sub
View Replies!
View Related
|
TRACKER
