Teach Yourself Excel Lesson Data Validation - Data Validation

How to set data validation for user to key in 24H time format?

I am doing some conditional data validation and I have a huge chuck of data that I am pasting into the spreadsheet and I'm wondering if there is a way to make the pasted data auto-fill with the validated items?

I have two columns with data validation (keyword, description) where description is dependent on the value of the keyword. This all works fine and is setup properly. The problem occurs when I paste values into the spreadsheet (the data validation is still there), but a keyword that exactly matches an item in the validated list does not "auto-select" from the dropdown, which makes my second column not populate with the appropriate description options. Does that make sense?

For example, if I select the Keyword "Door" in column one, I get a Description dropdown with the options "Open" or "Closed". However, if I paste the word "Door" into the keyword cell, it does not register as validated data.

Another note (based on previous example), if I select "Door" and had "Closed" pasted into the second column, it does pick it from the list automatically.

Sorry if this is a confusing example, but I have a lot of pre-entered data, that I don't won't to go through each row and reselect the item from the list for each. Is there any code I can write to expedite this process?

Any input is appreciated! Feel free to ask questions if any of that doesn't make sense...

Thanks,

Rob

validation rules which i have created are not working when someone copy paste data in the cell in which i have written validation rules want to know is there any way out

I have a budget workbook that my team of project managers is using. On each tab, they input contractor or hardware or software needs and the budget; from a data val drop-down, they also select the associated project/sub-project, which they input on a separate sheet.

The problem is, many of the PM's CHANGE the project info after they've started to input contractor/HW/SW info on the subsequent sheets.

For example:

Project Sheet

Project ABC
Project DEF
Project GHI

Hardware Sheet

Project Hardware Cost
Project ABC (DataVal) Sunfire V880 5.0

Later, however, they might change "Project ABC" on the Project Sheet to "Project ABC - Phase 1". The problem is, the subsequent sheets don't update automatically, and then my roll-up calculations go awry.

Any ideas on how update the subsequent sheets, like Hardware, if/when the PM changes the project info they've entered on the Project sheet?

-josanbabu

I have data validation setup on multiple tabs in a workbook. After that worked for all tabs I am trying to setup a summary/driver tab which would allow a user to change all the validation boxes at the same time. This does not seem to be working.

I added the cell from the summary tab to each validation range throughout the workbook but the validation cell does not change when I change the product on the front page.

Any ideas or help to solve this problem.

Basically I am trying to make the workbook so a user can setup one product throughout the workbook from a driver page but then also drill into different products if they choose to do so on each individual tab.

Thanks.
Brandon

Quite a few months ago I created a spreadsheet whe

The content of cell A1 was driven from Data Validation pulling information from a defined list on another spreadsheet.

The content of cell A2 was also driven from Data Validation but it's list content was defined based on the selection made in cell A1.

Basically in A1 you could select 1,2,3,4 or 5 from the drop down list and then in A2 you could select a,b or c if A1=1 and garage, brick, cat, mouse if A1=2 etc... etc... etc...

I forgot how I did this. My problem is that if I select cell A2 and select Data/Validation it won't let me see what custom formulas or what have you that I used to restrict the list content for this cell. I get the error message "The selection contains more than on type of validation. Erase current setting and continue? [OK] [Cancel]".

Is there an alternative method to determine the cell Validation restrictions? If not can someone give me a hint on how I originally accomplished this...honestly it was pure luck when I got it to work the first time.

Hi Friends,

I validated the cells through data validation. Some with Lists, some with Max/Min values, etc.

Whereas, when we copy and paste data from other sources, the validated cell accepts any value.

for example: a column validated with a Gender list - BOY or GIRL.

When copied from other data, it accepts anything.

Can any one give suggestion.

acsishere.

I have a cell with a data validation list in it. I can click on the cell and then make the selection I want from the list.

However, I want that selection to drive the list that become available in the next cell.

For example, in a cell with range name (Country), I have a data validation list with the choices of
USA
Italy

If USA is selected, I want the next cell with a range name of (City) to have a data validation list of
New York
Atlanta
Miami

If Italy is selected, I want the next cell with a range name of (City) to have a data validation list of
Milan
Rome
Venice


How do I control future data validation lists with the choice made in a data validation list? Let me know if I didn't explain this well?

Hi,
I have a procedure that runs on Worksheet_Change. (Only if the target.column=2).
In that same column, each cell has Data validation to prevent the input of any wrong value/text.
My problem now is that even if the data validation stops the input, the Worksheet_change procedure runs.
is there any form to catch if the data validation stops the input. (And so stoping the Worksheet_Change macro from running?)

Thank you very much.

for some reason I can't get it to work it wright
even copiying the code from the help file will not work

any clue what could be going on?

With Range("e5").Validation
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertInformation, _
Formula1:="5", Formula2:="10"
.InputTitle = "Integers"
.ErrorTitle = "Integers"
.InputMessage = "Enter an integer from five to ten"
.ErrorMessage = "You must enter a number from five to ten"
End With

error number -2147417848
error helpcontext 1000440

error description : Method 'Add' of object 'Validation' failed