How To Validate If Data In A Cell Conforms To Data Validation Rules In The Cell

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


ADVERTISEMENT

Data Validation Rules Not Moving With Data

Feb 24, 2014

creating a spreadsheet for work which is almost working a treat Unfortunately, when a row of data moves from one sheet (Queries) to another (Archive), data validation is lost. Initially I thought I daidn't have it set up on the Archive sheet, but on moving it back to queries (by use of a macro) the validation is still not working. how to keep validation rules WITH data when it is moved please? I will upload my file when I get home from work....network restrictions prevent me doing it here!

View 8 Replies View Related

Applying Data Validation Business Rules

May 4, 2012

I am in charge of validating 800+ excel reports coming from our vendors each month. I use excel data validation feature everytime I receive a new file. Data Validation feature is native to the excel workbook and cannot be saved to be used with other workbooks (at least; this is what I think).

What is the best way to write a generic validation script that I can run on any workbook. I am thinking somewhere along the lines of a test script that looks for specific data, data format, required data etc.

Here are some of the rules I have to validate against:

Cells in Column 'A' must contain data, reject if not populated.Cells in Column 'B' must contain a specific value (cat, dog, car), reject if other.Cells in Coulmn 'C' must contain (CA,AMM) only if cells in Column 'D' = 'Yes'Cells in Coulmn 'E' must be in proper casingAddress must use U.S. Postal Service standardsZip Code must be 5digits+4Exclude formatting charecters like '( )' from phone numberMinumum age must be less than or equal max ageMaximum age must be greater than Min ageCheck for populated data in cells, else reject file.Date must be in 'YYYMMDD' formatTime must be in HH:MM:SS' or 'MM:SS' formatID must contain 7 integersThis is a sample that represent 70% of files recived.

View 1 Replies View Related

How To Create Macro In Order To Protect Data Validation Rules

May 15, 2014

create macro in order to protect Data Validation rules.

e.g. If user copy and paste cells from others source which is not in the same validation criteria or not contain any validation rules, the existing validation will gone.

so, is there any macro which will be able to automatically run to prevent the data validation?

View 7 Replies View Related

Copying Data To Cell With CF / Rules Will Change On Its Own

May 4, 2014

I had previously posted a query on deconstructing 2 set of tables (with similar heading)(in sheet 1 & 2) & combine them side by side with similar heading (in sheet3) so as to use condition formatting to highlight the differences & visually i can compare them easily.

1 group is 2 tables to compare. I have about 30 groups. So after creating the 1st group, i intended to use it as a template, without having to create the conditional formatting again & again. But after copying, the conditional formatting rules change. I am not sure why.

I attached a sample of my template for reference.

Sheet 1 - Table 1
Sheet 2 - Table 2 & part of Table 1
Sheet 3 - combine both Tables with similar column header side by side

in Table 3, cell B3 rules are "=C5<>B5"

SO the problem is when i copy data from Sheet 2, Table 2's Column Header B (cell f9 to f421) to Sheet 3, Column Header B2 (cell c5 onwards) the rules change to "=C5<>XFC5"

View 7 Replies View Related

Nested Data Validation: Figure A To Enforce Dual Data Validation On A Single Cell?

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

Data Validation: Can I Use Data Validation In A Cell That Has Formulas In It?

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

Data Validation To Ensure Like Numbers In Cell A Has Same Date In Cell B

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

Copy Cell Value To Cell On Different Tab That Has Data Validation

Sep 11, 2013

I have a cell in Sheet1, C5 that is a formula that returns one of 2 values.

I have a macro that copies and pastes values from this sheet into Sheet2, however I cannot get the cell V3 in Sheet2 to have the value of C5 in Sheet1.

There is a data validation list of 3 choices in V3, two of which are available from Sheet1 C5, but I want the 3rd option to remain manual.

I have been playing around with it and even tried the manual recorder, but no luck

Here are some of my attempts:

Sheets("template").Cells(3, 22).Value = Sheets("New Details").Cells(5, 3).Value

or

Sheets("template").Activate
Sheets("template").Select
Range("V3").Value = Sheets("New Details").Range("C5").Value

Neither of which have the desired effect.

View 1 Replies View Related

Two Data Validation On The Same Cell

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

Data Validation Based On Another Cell Value

Aug 20, 2014

I have already set up a data validation, if if you pick a region, you only get that regions items in the next column.

A
B
C
D
E

[Code]...

But what I would like to do is in Column E(for this example) have 3 extra lists for Location in each region. So if I pick S in A, then in E I only want the Southern options.

I have checked out this website, but I cant seem to work out how to add the third dependence [URL]....

View 5 Replies View Related

Data Validation Based On Cell Value

Jul 14, 2009

I am trying to set the data validation on a cell based on another cell value

So basically if the User enters say CAN in cell A2, cell B2 should use "listCAN" range as the data validation list

and if i enter US in A2, B2 should use "listUS" as the data validation list

View 6 Replies View Related

Data Validation Contingent On Another Cell

Nov 24, 2009

How can I create a drop down list, where the items available for selection in this drop down list are contingent upon another selection made in a seperate drop down list.

View 2 Replies View Related

Use If Statement In Cell That Contains Data Validation

Sep 29, 2011

I have a cell containing data validation list and what i want to do is that when i choose the 1st value from this list .. a number (for examole 1) appears in another blank cell .. and if i choose the 2nd value from the list .. another number (let's say 2) appears in the blank cell.

So how to use the if statement in a cell that contains a data validation ?!!

View 7 Replies View Related

Check If Cell Has Data Validation With VBA

Nov 15, 2008

How can I check if a cell has data validation with a VBA procedure?

I want to do this because I am using a procedure to add data validation and I get an error if the cell already contains data validation.

View 9 Replies View Related

Apply Data Validation If Cell Value Is < X

Feb 2, 2009

I'm building some work life balance spreadsheets for employees to show satisfaction levels / hours etc...

For the satisfaction piece - users will enter a value between 0 and 5 with 5 = happy as larry and 0 = ready to quit. Next to this field is a comments box. This comments box only needs to be populated (from a data validation list) if the staff are unhappy.

I need the file to prevent users entering a value less than 3.99 without selecting a comment from the validation list. Users will go into the file and update for their own lines so the check can be written into the save function maybe???

View 9 Replies View Related

Data Validation :: If Cell A2 > 0 Have Cell A1 =1

Aug 13, 2009

Is there a way to use data validation for the following

cell A1

If cell a2>0 have cell a1 =1

data validation will be in cell A1

View 9 Replies View Related

Data Validation List Based Off Cell Value

Apr 25, 2014

I'm trying to have a Data Validation list issue where I want to populate a second list based upon the value of the first list.

In this instance I have a Department in the drop down and Employee Name in the second drop down and I want people to only see Employee names if they're allocated to a particular Department. I've been able to do the list using the INDIRECT function, however that only works when there's only a small sample of both names & depts, however I'm looking at populating this document with around 3000 people and over 250 departments, so I would like to be able to use Column J on the Source Tab to populate the details on the Manager Entry tab.

I have attached a sample spreadsheet if I've not explained very clearly.

TIMESHEET sample.xlsx

View 9 Replies View Related

Multiple Data Validation Entries Into One Cell

Jun 2, 2014

Is it possible to create a formula using a Data Validation List where it will add a new entry to the existing entries in a cell?
For example: I have a data validation list with the names of Pete, Chris, Bruce, Carly, Megan, and Becky. With the normal data validation set I can select one entry and it will be output into the designated cell. If I select Bruce then Bruce with be placed in the cell. Is it possible to set it up so that each time I click on data validation it will add an entry to the already existing entry? E.g. I have already selected Bruce then I decide to add Becky, I want the output cell to show both Bruce and Becky.

I know I can do this with a formula that will place all the results from different cells into a single cell, but I would then have to create multiple data validation entries of which I would rather avoid if possible. Not to mention I don't remember how to do this formula anyway......

View 14 Replies View Related

Data Validation Cell And Address Of Selected Value?

Aug 7, 2014

is it possible to find the number of row for selected value in cell with data validation?

for example:

sheet2
cell G13 have data validation format
Allow = List
Source = 'sheet1'!E:E
Ignore blank = yes

sheet1
columnn E contents file names with hyperlink
FileName1.txt
FileName2.txt
FileName3.pdf
.....
FileNameN.txt

I choose FileName3.pdf

View 2 Replies View Related

Data Validation Dependent On Another Cell Being Completed?

Jan 27, 2014

I've got a password protected spreadsheet with only certain cells unlocked to allow users to complete them. I need the following to work on Excel versions 2003, 2007 & 2010.

Cell E3 has a drop down list with types of car to choose from. One of the car types is "Pool_Car".

Cell D12 should only be used if "Pool_Car" is selected from the drop down list. If any other car type is selected, and the user tries to enter a figure in D12, I need an error message to appear.

E3 & D12 are unlocked cells.

I'm happy for D12 to become locked & only allow it to be unlocked if "pool_car" is selected, but I've looked elsewhere, but can't find VBA or data validation to work.

View 3 Replies View Related

Special Data Validation And Cell Highlighted

Jan 14, 2010

I need a hint for one complex validation. In column B I am having emails and need to validate that after @ the extension is yahoo.com. If something different from that I need the cell highlighted.

View 4 Replies View Related

Using A Cell On Another Worksheet As Input For Data Validation

Oct 13, 2008

Situation: I have an Excel file with multiple worksheets. Each of these worksheets contain the same rules for data validation in a certain column (G).

Problem: If I want to add an item to the data validation lists I have to edit every single worksheet. I've tried making a seperate worksheet containing data validation items and using that as the source for the data validation rule, but Excel doesn't allow me to switch worksheets while defining the source.

In other words, I want to manage my data validation rules of multiple columns in multiple worksheets in one central location.

View 4 Replies View Related

Filtering Data Validation Based On Other Cell Value

Feb 17, 2010

I'm working on a few dynamic KPI charts and could use a little help with figuring out data valuation for the End Date value. In the attached workbook I am using data validation to pick a start date from a dynamic list (Dates) in E2:Exxx. Cell B2 has 2/8/2010 picked. I would like to have a drop down in B2 show the listing of all dates in E2:Exxx which occur after 2/8/2010, so the filtered list would be in the range E3:E49.

View 2 Replies View Related

How To Lock Or Protect Cell Using Data Validation

Jun 6, 2013

1- If I wish to write in the cell I can do it ?
2- When I do not wish to write in the cell I can do it (Its meaning protect the cell.)

View 2 Replies View Related

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 View Related

Reference Data Validation Cell From Another Workbook

Sep 17, 2008

I'm trying to link a range of cells in one workbook to a range in another workbook, both in a network drive. Everything is working out well except for the cells that have a data validation list on them. the formula linking one cell is ='G:Destination To[Workbook2.xls]SheetX!B13 so I'm just trying to get the value in the cell. I don't need the validation list to transfer just the value of what was selected. If I go to that cell and type something in then it transfers over just fine. If the validation list was used in Workbook2 then I get a #N/A in Workbook1 that is linking to the Workbook2 cell.

How can I link the cell with a data validation list in it to a cell in another workbook so that I can get the data in the cell (whatever was selected from the validation list)?......

View 4 Replies View Related

Prevent Empty Cell With Data Validation

Nov 17, 2007

I'm looking for a formula which will enable typing (in cell A1) positive numbers between 0-1000000 but will prevent the user from leaving that cell in an empty state. Empty cell means: cell with space(s) or deleting the legitimate value that is, already, in it).

I must emphasize that I know how to handle this by VBA code and/or by using a "named" pre-defined list of values + un-checking the "ignore empty cell" option - but I would like to know, once and for all, the solution WITHOUT using Macro and/or 'List' (if any..)

I tried that, without success:

=AND(A1>=0,A1<=100000,NOT(ISBLANK(A1))

View 8 Replies View Related

Prevent Copy Paste Over Data Validation Cell?

Mar 27, 2009

I set up a spread*** with drop-down menus for some of the cells. The user can only select values from that list and an error message pops up when something is typed it that is not in the list (via Data Validation

Error Alert).

So this works all fine ... except if the user copies and pastes a cell with a different drop-down menu into a cell overwriting the existing validation.

I cannot totally lock the work*** and if I lock the cells with the drop-down menus nothing can be changed. So how can I prevent this from happening? Is there a way to block all copy/paste operations that are copying the validation? I know that with the Paste Special I can have cells copied without the validation.

View 14 Replies View Related

Lock Data Validation And Yet Permit Cell Editing?

Apr 22, 2009

What I'm trying to achieve is to be able to lock the text length of a column so that users can enter text data into that column but not exceed the allowable length for our purposes. I've found a way to use auto loading macros to make a popup if they exceed the set length.

However, that is useless for our purposes because 1) pasting data into the cells as opposed to manually entering data overrides the validation just as it does when you set it with the data validation menus, 2) it requires that macros be activated in the end user's computer which we cannot guarantee will happen. If you lock in the data validation by protecting the worksheet, the end user can't enter data. Do you know of a way to lock in the data validation without using active controls like macros, and yet allow the end user to enter data into the cells. (Perhaps a solution would be to somehow prevent the pasting of data into a cell forcing the data validation to be utilized?)

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved