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
ADVERTISEMENT
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
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
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
Jun 16, 2009
I want to have a four sheet database for a client list. Sheet one would be the "master sheet" and will have a column that has a validation drop down with the values "current client", ctive prospect",""dead deal", etc. I want the information from the master list to automatically move to the corrisponding sheet(s) when the validation is chosen.
View 9 Replies
View Related
Apr 3, 2009
I have attached a sheet that I'm having an issue with. Columns D and E on the Dim Calculator sheet have a data validation formula in the cell. It works in the sense that it pops up if they are outside the parameters that I want them to hit. However, if the user clicks the Cancel button they can just keep going on even though the entry in the cell is aganist the validation.
Example: Columns D and E cannot be larger than Column C. Currently now if Columns D or E are larger than Column C the validation error pops up. But I can just click on cancel and keep going. That is what I want to prevent.
Is there a way to grey out the cancel button?
Should I do something else within the validation itself?
View 3 Replies
View Related
Jul 12, 2006
I have 2 input boxes. Input Box 1 asks for user's Date of Birth. Input Box 2 asks for user's name: Pretty simple right! They will loop until following 2 conditions are met: If the user enters something in them OR user presses Cancel. If they are empty and Ok is pressed then keep on looping.
View 4 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
Oct 5, 2009
Ive been having a little issue with this spread sheet. The problem ive got is I cant get the validation to work where it only allows one yes in a bunch of cells and gives an error message, heres an example of what im talking about.
View 3 Replies
View Related
Nov 29, 2011
Validation on Excel
The User enters the date in cell D5 - I would like excel to validate the date based on the following rules:
1. Format "dd/mm/yyyy"
2. Prevents entering any weekend dates
3. When the user enters the date it can be the previous or current months date range
I have tried several ways in excel but I can't get anything to work. If an error occurs I want an alert message to appear.
View 7 Replies
View Related
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
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
May 5, 2014
I am working on a project that has 5 worksheets. I have been able to figure out everything else I need to do but this has me stumped. I have data in Sheet1 A6, that i want to place in Sheet2 A6, Sheet3 A6, Sheet4 A6 and Sheet5 A6 and keep data and formatting(BOLD AND UNDERLINE). So I change Sheet1 A6 and the other 4 sheets change also. I'm using Microsoft Excel 2007.
View 1 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
Sep 26, 2013
I'd like to create a drop down list in data validation from a column of data that contains numerous duplicates.
For example, let's say column A contained hundreds of transactions with either North, South, East and West, how could I create a drop down list in another cell that only had four selection options?
View 5 Replies
View Related
Jan 9, 2014
Essentially, I would like a user to be able to select a PUB from the Data Validation drop down in row 2 of the PUB RATES sheet so that the corresponding information in the DATA sheet autofills.
Currently, this works only for the first column under each PUB when selected and this fills across all 4 columns (rather than the respective information for each column filling).
Also, the Data Validation dropdown includes blanks which I would like to exclude.
I have used a Range Name for the Data Validation of each PUB so that these can be drawn from a separate sheet as I don't want all the DATA content on the same sheet as the PUB RATES content.
View 4 Replies
View Related
May 28, 2014
I want to load the actual data exist in the rows by selection value from the data validation list.
E.g I have two worksheet in one excel file. One has a data activities of persons with their name like two columns i have in which one exist the name of person and second exist the activities which they perform.
On the second sheet, i made a data validation list of all the person names
Now my requirement is, when i select a person name from the list, load all the data from the 1st sheet to second sheet. Is this possible without VB code, because I want to share it on the Google sheet with my boss, where VB sheet is not supposed to work.
View 13 Replies
View Related
Jan 12, 2010
Is there a way to use Data Validation that will pull data from a source list and also be able to type in additional data or just new data in same cell?
View 3 Replies
View Related
Jul 27, 2009
I have a excel sheet with values correcponsing few names. I have a list for a Data Validation drop down. How to get all the data row wise for the name selected in the drop down.
View 3 Replies
View Related
Oct 27, 2009
I am trying to learn the concepts, then apply them to a set of actual worksheets. I am trying to use named ranges from one worksheet in another worksheet. The named ranges need to expand. Although I can get the add data to a dummy worksheet to work within the same worksheet and I can even get a named range to work in the foreign sheet.
My problem is, when I can end up adding the additional data to the named range, it will only add one name. I have a number of named ranges that are referenced on various worksheets. The idea is, certain named ranges should be able to expand so I donft have to type them all in. (There could be hundreds of manufacturers or models.) I would also like them to be able to sort themselves after additional names are added to the named ranges. I would also like them to auto populate as well. I am using Excel 2003 SP3 (11.8307.8221). I have enclosed the file. Drop down lists (named ranges) are on the worksheet called DROP DOWN LISTS (LOCKED) **ITfS NOT CURRENTLY LOCKED**.
The data validation issues are on the worksheet called Rev7.0 Wood (Basic Sheet.) There are other instances to use these, but I hope to learn to do them myself on the other worksheets. Of particular interest is Column f.................
View 3 Replies
View Related
Nov 1, 2006
In column A I have a list of text. There are blank lines in between the cell that actually contain text. What I am trying to accomplish is create a validation list that will give me only the cells with text in them and ignore the blanks. For example in column A1:A7 I have the following text:
John
Mike
Tony
Jake
My validation list will return those names but will also give me the blanks in between the names.
Is there any way to ignore the blanks?
View 4 Replies
View Related
Sep 26, 2007
I would like to know if it's possible to populate a data validation list based on what is selected from 4 validation lists?
for example:
On sheet1:
If 'Group1' is selected from data validation list1 then data validation list5 will show a list of all items from Group1. If 'Group2' is selected from data validation list2, then data validation list5 will display all the items in 'Group2'...
(I do not want to use a combo box for this)
View 9 Replies
View Related
Jun 3, 2009
I would like to utilize data validation to create a drop down list from a list of 100 rows that changes monthly. Currently the list is set to draw data from A1:A100, however depending on the month, different cells will have data in them. What I don't like is currently the blank cells create gaps in the data validation drop down list and make it longer than it needs to be. Is there any way for excel to automatically just display cells with data in them in the drop down?
View 9 Replies
View Related
Jun 28, 2009
have created a list obx using data validation. My question is, is there a way to limit the data available on the list.
Example: sheet 1
A1 A2 A3
Address A
address p
address s Date
Sheet 2
has data validation list that consists of all of A1 a named range called "address". Everything works great.
I now want to be able to limit the available choices in the list depending on what cell the user is in
example: if user is in sheet2 a1, i only want them to be able to pick address that have an "A" or "p" in cell A2 on sheet 1
I tried to acomplish this using conditional formatting, I format my named range with different colors, but the colors do not carry over to the data validation list on sheet2.
View 9 Replies
View Related
Sep 15, 2006
I have limited ability with Excel, and I'm trying to create a database using the Data/Form menu choices. My problem is I only want specific data in two of the 20 or so fields. When I use Data/ Validation and direct data entry, I get exactly what I want, EXCEPT that the choices don't come up when I use the Data/Form. Users can enter anything they want, which defeats the purpose. I tried to build a user form to use for data entry, but I don't know how to get it to add items to a database or lookup items like the data/form process does. I also want to add a few macro buttons to the form to do other things.
I am pretty sure of a few things: 1) This should be pretty simple. Anyone knowing VBA would probably know how to do this in minutes. I found some code that did a simliar function, but couldn't interpret it to fit my needs, which brings me to 2) I'll never use VBA or complicated macros again. I don't have the need for them. This is just an isolated incident. Therefore, signing up for a class would be useless to me. I tried to locate some advanced Excel/VBA classes, hoping to find an instructor or even a student who would welcome a simple challenge, but there are none available.
View 5 Replies
View Related
Apr 17, 2007
about data validation drop downs.
I have 2 columns that need to be filled in once a name is selected from the data validation drop down. The data is to the right of the name in the source area.
Column A Column B Column C
1 Name 1 Skill 1 Phone #
2
3
4
This is the format, Every row in Column A has a Data Validation Drop Down, I need Column B and C filled with Data from the selected name from Column D (the data columns D-F)
Column D Column E Column F
1 Name 1 7 555-1111
2 Name 2 5 555-2222
3 Name 3 3 555-3333
4 Name 4 4 555-4444
So when I select say name 3...
Column A Column B Column C
1 Name 3 3 555-3333
2
3
4
View 3 Replies
View Related
May 20, 2014
I'm essentially getting a lot of data at the moment, which has a few orders people have made on my website.
Essentially, think order id, address etc and then all the products the customer has ordered.
However, the part which includes what the customer has ordered creates multiple rows of data, with the order ids etc duplicated. What I need to do is consolidate this into 1 row. So to add additional columns instead of rows.
The reason fro this is I want to mail merge the data into an invoice and mail merges work of 1 line of data at a time. I've attached an example, any way to do this?
(Attached to this post / or linked here: [URL] ....)
View 1 Replies
View Related
Dec 11, 2009
I have 3 columns Z, AA, AB. The heading for Z1 is A, AA1 is B and AB1 is C
In column Z2:Z2000, there is a mix of A, Bs and Cs. I want A to stay in Z1 column, Bs to goto AA1 and Cs to goto AB1, also I want this added to a macro that I previously created, so everything happens with one push of a button
Now for save, not sure if this is possible or not, if I can have this added to the macro as well that would be great. When I push my macro button, the file saves to Dec (todays date) DB (81).xls The number 81 is the total count of A, Bs Cs, this # will change depending on how may A, Bs and Cs there are. I really hope there is a way of doing all this
BEFORE
A B C
B
A
A
B
C
C
C
A
A
B
C
SHOULD LOOK LIKE THIS
AFTER
A----B-----C
----- B.....
View 4 Replies
View Related
Dec 13, 2012
Essentially, there are two columns I am dealing with. One is "Sales Rep" and it lists all of the sales reps employed by the company. The other is "Zip Code" and that will list all of the zip codes that sales rep is responsible for.
Now, I have a row of data, all of those zip codes listed out, that each rep is responsible for, but my supervisor wants all of the zip codes listed in one cell, in that second column. Example: (02018, 34098, 16711).
The commas are not necessary, but is there any way to get this done other than manually entering them?
View 6 Replies
View Related
Jul 24, 2007
I have a workbook with 5 Tabs. One of these tabs is "Completed" (for completed work) The other tabs are names of Managers and the tabs contain information about who is doing what work for the Manager and information about it.
What i would like to do is in column F on every sheet is the "status". I would like when the "status" is changed to completed, to have VB code move that entire record to the Completed tab.
I think its possible i just don't know how to do it.
View 14 Replies
View Related
