Data Validation - Blank Cell Not Working
Sep 11, 2013
I have a tracking sheet... in that I got in column C say "priority" there I have a drop down list date say P1, P2, P3
Now I need to restrict that if a user enters a data in the C2 and trying to enter a data in D2 leaving the drop down list data blank in C2. A error message saying "pls select priority forst" etc...
I got the following solution from moderator:
You can apply the Custom Data Validation formula =LEN(C2)>0 to D2, making sure that Ignore Blank is unchecked.
It works but, when I enter something in that cell it gives the error message and when I click OK or Cancel it just ignore the conditions and leave the data what ever I entered and moves further.
View 1 Replies
ADVERTISEMENT
Apr 13, 2011
For some reason my data validation cell is not working when I first open up the spreadsheet.
I have to go to data validation, open it and just hit ok, then I am able to use my drop down list.
My data is this
"=OFFSET(name,MATCH(LEFT(B2,LEN(B2)),LEFT(address,LEN(B2)),0),0,SUMPRODUCT(--(LEFT(address,LEN(B2))=B2)),1)"
View 5 Replies
View Related
Mar 28, 2013
I have a data validation list does nothing upon start up (not working). The formula is:
=OFFSET(E$1:E$5,MATCH(C94,LEFT($B$3:$B$87,LEN(C94)),0)+2,0)
If I go into data validation and just click OK (leave formula as is), it starts working again, but the next time I start up the same thing happens.
View 3 Replies
View Related
Jun 12, 2014
I'm trying to add code to validate the format in which users are entering in a date within my userform, and I am also trying to validate if they enter in a specific value within a combobox, they will be unable to enter data within a textbox further down in the userform. Here is how my code is currently written, but neither my date or data validations are working. I have bolded the new coding I added to an existing code that was created by someone else at my job prior to it being given to me.
[code]
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
[Code].....
View 14 Replies
View Related
Jul 23, 2014
I need to compare 2 cell with 1 specification reference.But the function can't give return value as per required.Both 2 input cells using Data Validation List.
Please refer attachment for some examples : matching.xlsx
View 2 Replies
View Related
Aug 21, 2012
I have the following function in cell (table 2 column C) where I want to find a contact name from my table TMI if 3 criteria are true (client, account, NAM) For each contact I have 1 true combination of client/account/NAM but for each client/account/Nam I can have several contacts.
{=OFFSET(TMI[[#Headers],[Contact]],MATCH(1,(TMI[Client]=$B$1)*(TMI[NAM]=C3)*(TMI[Account]=B3),0),,COUNTIFS(TMI[Client],$B$1,TMI[Account],B3,TMI[NAM],C3),1)}
The function is working well but shows only the first contact name if I have 3 contacts for the same client/account/NAM. (I pressed Ctrl+Shift+Enter)
When I press F9 on the function, it shows all the contact names! so they are somewhere.
Hence, I have copied and pasted this function into data validation / list in the source but it came up with an erreur.
Table TMI:
A B C DClient
Account
NAM
Contact
Comments
[Code]..
Table with offset function:
A B C D
Chi
Account
NAM
Contact name
Comments
[Code]..
All client, NAM and account lists are dynamic !
View 2 Replies
View Related
Apr 22, 2003
I have set cell validation to use a named list and checked 'In-cell drop-down'. The validation works but the drop-down is not working.
View 9 Replies
View Related
Jul 10, 2012
I have the following code to check the date of birth entered into cell C18 and to alert the inputter if the age is either under 16 or over 25.
VB:
Sub AgeValidation()
Dim age As Integer
Dim dob As Range
Dim AgeMsgAnswer16 As String
Dim AgeMsgAnswer25 As String
[Code] .....
It works to some extent in that the correct message box pops up if the age is under 16 and similarly if over 25. If answering Yes on either message box then the correct thing happens. Its what happens if the inputter selects No thats not right. If the age is under 16 and the user selects No in answer to "Is this correct?" then the code is clearing the cell contents and showing the calendar again but is also popping up the 'Over 25' validation message box which then won't go away until Yes is selected. Also there are then multiple copies of the calendar open.
What I need the code to do is look at the date selected from a popup calendar in c18 and decide if that age is within the 16-25 year old range. If it is outside that then the inputter needs to be alerted to it. I can't use the inbuilt data validation because there are some scenarios where it would be acceptable to have an age outside of that range but we want to cover inputting errors as well as double checking the age.
When a msgbox pops up to alert the inputter and they choose "Yes" to say the date of birth is correct then I want the focus to go to cell C20 ready to input the next piece of information.
When the inputter selects "No" on the message box, then I want the original date to be deleted and the calendar to reappear so they can select another date. So effectively resetting the field so they can start again choosing a date like when they first entered the cell.
View 4 Replies
View Related
Dec 3, 2012
I'm trying to validate a cell in Excel 2007 that should contain only two letters.
Formula:
Code:
=IF(AND(LEN(A1)=2,ISTEXT(A1)),TRUE,FALSE)
When the value of A1 is JK, the result is TRUE
When the value of A1 is 12, the result is FALSE
When the value of A1 is 3K, the result is TRUE
When the value of A1 is K3, the result is TRUE
When the value of A1 is 123, the result is FALSE
When the value of A1 is JKL, the result is FALSE
When the value of A1 is 3, the result is FALSE
When the value of A1 is K, the result is FALSE
The LEN function works as expected, but the ISTEXT function does not, whenever the cell contains a number and a letter. I've tested the LEN and ISTEXT functions separately, and get the same results.
Is it me, or is it Excel?
View 7 Replies
View Related
Aug 19, 2009
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 Related
Mar 17, 2006
I have a spreadsheet with some cells setup with a drop-down list containing Y, N or N/A
This is being used on a TabletPC but if I make a mistake or need to change back to a blank field I have to invoke the soft keyboard, activate the cell and hit backspace then close the soft keyboard - quite a long-winded procedure just to change an incorrect choice!
What I would like to do is add a blank to the list so if I have to revert back to a blank I can just use the stylus to choose a blank from the chooser list.
How do I add the option of inputting a blank from the Data Validation List bearing in mind I am using the Data Validation Source box for entering my choices directly and not specifying a range of cells?
I have tried adding "" and even a space to no avail.
View 11 Replies
View Related
Aug 21, 2009
I have a list in one worksheet which comes from "=SALESMEN!$D:$D" but the list is extremely long with blank values. How can I make the list only show values from column D which are non-blank?
Currently the list goes up to 30, however I want to use all of Column D from the SALESMEN worksheet, that way if I add to it, the names will automatically be added to the list in the other sheet.
View 10 Replies
View Related
Mar 4, 2003
I am using Data Validation and I don't want the user to go past a cell without putting in an entry. When I leave the box (ignore blank) unchecked it doesn't do anything after I have protected the document.
View 9 Replies
View Related
Mar 19, 2013
I am currently working in an excel file for which i want to create a macro. I am using colomn F as the base from which it should be determined whether a row should be copied. In colomn F the user can select three options through data validation. If 'completed' is selected, the whole row should be copied to the second sheet, and each copied row should be pasted to the next empty row. The data on the source sheet starts at row 11 and should be copied to sheet 2, starting from row 11 as well.
View 5 Replies
View Related
Jun 17, 2014
I have a list that has blank rows mixed in. I'd like to return a list without those blank rows to be used as a dropdown menu for a budgeting tool.
I tried using the offset formula from the link below but this just cut off my list after the first blank rows showed up.
My current list looks something like this:
Code
5000-Salary
5000-01 - Hourly
View 3 Replies
View Related
Aug 26, 2009
Would like to know how to place validation on cell a2 to NOT EQUAL BLANK.
View 11 Replies
View Related
Aug 10, 2006
I would like to validate data but cant quite get the formula right. I have a cell (say F7) that cannot be blank if (say E7) another cell equals a text value (say "S"). Anyone have an idea? I am stumped at this seemingly easy task. I am applying the validation below to F7.
I forgot to add the valid combinations:
E7 = P and F7 = Entry: OK
E7 = P and F7 = Blank: OK
E7 = S and F7 = Entry: OK
E7 = S and F7 = Blank: INVALID
I have tried the formula: =IF(AND(E7="S",ISBLANK(F7)),1,0) within the validation window but blank entries are still allowed. I put this code in adjacent cells as a formula, the values are correct either 1 or 0.
View 4 Replies
View Related
Apr 3, 2009
I have attached a sheet that I am working on. I want cell G1 to be less than or equal to 165. That cell contains a formula. If the formula takes the number to over 165 the validation is allowing it.
View 2 Replies
View Related
Jun 20, 2008
I can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.
View 10 Replies
View Related
Jul 9, 2012
The code below is fired from a Worksheet Change Event on cell c18. The cell has a userform calendar control to select Date of Birth. What I want the code to do is check the age at the current date and if it is below 16 or over 25, then show the appropriate message. I cannot use data validation to not allow values outside this age range because there will be instances where a person's details can be added if their age is under 16 or over 25. I just need to alert the inputter in case they are unaware or have inputted incorrectly.
When the message box pops up, if they click Yes then the code should take them to the next cell for inputting. If they click No then it should stay in cell C18 and hopefully pop the calendar back up (I've not tested this bit yet).
The code as it stands brings up the "This person is under 16 years...." message no matter what date of birth is input.
VB:
Sub AgeValidation()
Dim age As Integer
Dim dob As Range
Dim AgeMsgAnswer16 As String
Dim AgeMsgAnswer25 As String
Set dob = Worksheets("Monitoring Form").Range("c18")
[Code] ....
View 3 Replies
View Related
May 13, 2014
I have a log that I have data validations on.
Column C validates on a list called "Division"
Column D is supposed to validate based on the selection in Column C, and give the list based on Column C.
I had it working awhile ago, then I went and changed the lists. Now, the lists are not validating appropriately (I'm not getting the expected drop down in D based on the selected in C) and I don't know why its not working anymore.
View 1 Replies
View Related
Jan 22, 2014
I have a UserForm with Combo Boxes, List Boxes, Text Boxes etc. When the OK button is clicked I want it to ensure certain fields are not left blank and notify the user (see code below). This works great for the combo boxes but not for list boxes. It seems to skip over that code. In other words, in the code below, if a manager is not selected, it doesn't notify the user.
Private Sub OkButton_Click()
'Verify fields are not left blank
If cboAgent.Value = "" Then
[Code]....
View 2 Replies
View Related
Sep 2, 2009
There is a range of quarters and relevant dates like from and till - means if you have 1 Q 2009 => it means from 01.01.09 till 31.03.09. I would like to have following in my excel. There is a validation for selecting Q from list, once you select Q, there is LOOKUP function displaying from and till dates. But however it's not working, in same cases it's working, but in most cases the from/till period is not relevant to selected quarter.
View 2 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
Jul 1, 2014
I'm using the following code to try to ensure a value is entered into a text box, but when I click over to the next text box skipping the first one entirely, I don't receive any message indicating the previous box is empty. I'm trying to make it so certain fields are required and others are optional. Here's the code I'm currently using:
[Code] .....
View 14 Replies
View Related
Dec 30, 2013
=SUM((DATA!$F$2:$F$3000=A3)*(DATA!$N$2:$N$3000="N")*DATA!$J$2:$J$3000)
This formula doesnt work but =SUM((DATA!$F$2:$F$25=A3)* DATA!$N$2:$N$25="N")*DATA!$J$2:$J$25) works. Thats because all the other cells higher than 25 have a reference to another cell. How to solve it? Maybe with SUMPRODUCT / ISBLANK? See attached file.
View 3 Replies
View Related
Oct 14, 2011
My Macro opens an excel file and I've to count the number of blank spaces for every filled column. I'm using CountBlank function for that.
But, the code fails at the countBlank line by saying Type Mismatch.
Code:
Sub Snippet_Code()
Dim apdata as excel.application, wbdata as workbook
set apdata = new excel.application
Set wbData = apData.Workbooks.Open(Filename:="ABCD.xlsx")
For colnum = 1 to 50 'Columns from 1 to 50
[Code] .......
My code fails at the BlnkCnt part. I also tried assigning the two cell addresses in two variables and passing the variables to the range method. But there again, I get the error of Method Range of class _Global Failed.
What might be the error?
View 6 Replies
View Related
Feb 15, 2014
I have 2 columns First Name & Surname. What I want to do is create a data validation list on the surname which results in the 2nd data validation list only showing the first names which link to one of the surnames.
i.e. If I selected Smith in the 1st validation list then I would only like to see 'Paul' as an option in the 2nd list
First Name
Surname
Paul
Smith
Paul
Jones
Tony
Phillips
View 1 Replies
View Related
Jan 10, 2008
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
View 12 Replies
View Related
Sep 14, 2009
can i do two data validation on the same cell
the first one to make a list form Define name "Lists"
and the other to make sure the name is not repeated
View 9 Replies
View Related
