Data Validation Features and Help in Excel

How, Please could someone point me to a good tutorial on data validation for
user forms?. I have a workbook that uses the INDIRECT method to restrict
options dependant on the first selection. I want to create a user form for
data entry, if I use the Excel data form from the menu bar, the data
validation is not carried across.

A url to advice would be great,
Thanks,
Mickey

I have added a Data Validation List to my excel worksheet.
The arrow icon indicating that the cell contains a drop-down list is only
visible when a user selects the cell. I would like the arrow to be visible at
all times. Is this possible?

Thanks.

Hi,

I have add eean input message box to aid data entry in a cell where there is
data validation.
I open the worksheet, reposition the message box, save the worksheet.
re-open the worksheet and when i click on the cell with data validation the
message box is in its original position (obscuring other fields). Anyway I
can make the position of the message box fixed?

Many Thanks.

Hi all,

I am creating a spreadsheet which will have data entry from different
users.

I want to give the validation in such a way that a combination of
values in 2 columns do not occur more than once.

Like say
A B
1 a
1 b
2 a
2 b
2 a

in above example the combination 2 and a is recurring. it should be
restricted by validation while entering the data itself.

can anybody please help

Regards
NC

I use data validation to select data entries. The width of the list box is
different in some worksheets. How do I control the width to expand when there
is longer text in the list range?

I assign "names" equipment for worksheet1 COLUMN 'B' that contain

A B
1
2 primary
3 secondary
4 tool
5 gauge
6 cutter
7
8
9
10
11
12
...


For worksheet2 I assign cell b2 as data validation

Allow -> list
Source = equipment
Ignore blank -> check


However, after I copy the data validation to the rest of the row, something
weird happen

From cell b3 onward, clicking on the drop-down show the last blank cell
first, instead of showing the 1st value "primary".
The next cell b4, however doesnt have the 1st value "primary"
Cell b5 doesnt have 1st and 2nd value, and so on so forth,

Hi there,

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.

Although not a betting man I would wager there is a simple 'fix' for this
but things are only simple if you know how in the first instance! ;^)

--
Thanks & regards,
-pp-

I'm using Excel 2002 and 2003.

Has anyone had occasion to set up a macro to do Find/Replace (or even
just Find) on the Data Validation Input Text/Title, Worksheet Objects,
or the like?

Or perhaps a macro to dump the Data Validation set up in a worksheet or
workbook?

Hate to reinvent the wheel.

I'm starting to think in terms of a "Power" Find/Replace.

Thanks!
Neva

Hi there

I would like to create a list based on data validation. The complication is being able to format this depending on the selection. I cannot seem to get a cell with data validation to change color - custom number formatting doesn't register an input which has been made from a cell entry using data validation.

Does anyone know how to do this at all? Your earliest response will be greatly appreciated.

Hello.
I have two spreadsheets.
The first spreadsheet I can successfully create data validation
with drop down arrow on the right of the cell.

But on another spreadsheet, the drop down arrow is invisible!
All validation still works though...but ideally you will have the drop
down arrow on the right side of the cell.

Can someone assist please....

I'm using a very long validation list for a drop down menu. Is there anyway to setup the drop down so that one could start typing and the list would immediately eliminate to less options?

For example, my list consists of 3000 plant names. Instead of scrolling down to choose a plant that begins with a letter P, is there anyway to start typing "P" in the cell and have the list pop up and jump down to entries that begin with "P"?

I know I've entered data with such a function but can't figure out how to set it up?

Is there any way to change the font size of the entries in a drop down list generated by data validation?

Hi Guys,

I wanted to enter a data validation for a column in a spreadsheet I am designing.

The problem is; if I use the Time in the 'Allow' combo box and then between in the 'Data' combo box, I set the start time as 0:00 and I wanted to set the end time as 999:00 as I am dealing with total hours worked and not hours within 24 hours if you get what I mean.

The problem is sometimes I need to enter large hours such as 521 hours for example.

Is there a way that I can set maybe a wildcard format so that as long as the time format is h:mm it will accept it.

The Time cell is formatted as: [h]:mm.

My second problem is with dates. I have another sheet on the spreadsheet for checking vacancies. in the first column is 'Date from' and the second column is 'Date to' for the required vacancy for temp staff. The third column then calculates how many weeks the temp member of staff is needed by simply subtracting the 'Date from' from 'Date to'.

I wanted a Validation in the 'Date to' column so that if a date is entered that is lower than the 'Date from' date then it will flag up an error as I don't want to be dealing with negative dates.

How can I achieve this?

Thanks in advance for any help offered.

Hi
I have data validation in a column range. One cell in this range has the data validation arrows always visible. The arrow positions are slightly off and when I select this cell a second set of arrows appears. I have tried clearing data validation for that cell, closing excel and re-booting, copying the wks to another workbook - nothing works - is this worksheet corrupted?

Thanks for the help.

Richard

I use MS Excel 2003 and today ALL cells that had previously functioned as drop-down menus with the data validation function referring to a listed range suddenly stopped working. They still validate, they will only let you enter the appropriate values, that is IF you know what those are, but whereas before you would click the cell and the pull down arrow would appear and you could click that for the list, now nothing happens.

You can even go to the data validation info for each cell and the settings are all there, the drop-down box is checked, and the correct range is still in place. Also you can make a new data validated cell with a drop down list and it works perfectly, but there are hundreds of others that are now seemingly broken.

It seems incredibly strange to me, and I opened a version that I had saved just a bit earlier (but before adding a whole lot of important data since then) and the pull-downs are working fine.

Has anyone heard of such a thing or why it might happen or better yet, how it might be fixed?

I am very careful to save my files and handle them with care. I have been working on this one for years. It is just so weird for something so normal to simply cease to function. I closed and reopened the program, rebooted the computer, tried the file on a different machine, just hoping it was a glitch or something, but all was to no avail.

I would really appreciate any insight anyone can give me. Thank you so much in advance if you can offer any clues at all.

I have a workbook that I've been working with for a long time, and I'm trying to add a dropdown list with a data validation to one of my worksheets. Data validation works in every other worksheet, except for one.

Any ideas?

Thanks for the help.

Hey There,

I'm having a problem with a data validation list. The ammount of entries in this list will fluctuate week over week, so my range from the list needs to be O2 to O300. Those cells are populated by another formula.

I go to Data - Validation, select List, input the cell range and make sure the "Ignore Blanks" check box is marked, but it is still adding all of the blanks into the drop down box it creates.

Does anyone know a way to get it to filter out those blanks?

Thanx a million in advance!!


Nevi

I am running into an odd issue and hope someone might be able to shed some light.

I have created several lists on one sheet in a workbook and have assigned named ranges to each individual list. So far, so good.

On various other worksheets, I have created Data Validation rules to allow users to select the relevant data from drop-down lists. (using formulas such as =List1, =List2, etc. in the Source box to capture the named ranges I had created).

Everything works perfectly...EXCEPT for one worksheet, which continues to give me the error message, "You may not use references to other worksheets or workbooks for Data Validation criteria."

Can anyone explain why this error would only occur on one worksheet and work fine for all the other sheets? I'm perplexed!

Thanks for your help!
Heather

Hello -

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.

Thanks for any comments!
M.Velmurugan

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?) Thanks in advance for any insight or advice you might have.

i require a formula for custom data validation to permit entry 5 digit numeric characters only.
No spaces, or alpha permitted.
Note that the cell format will be text, to enable leading zeros to be entered & displayed (as permitted)
Thanks.

refer previous post for similar for aplha only.
http://www.excelforum.com/excel-work...ml#post2007297
I was unable to adapt for my new requirement.

Any help would be appreciated

I have created an excel template file that users will be pasting data into, from other excel spreadsheets. The first column of data is numeric (integer). Somebody pasted data for which the first row contained a number that had an warning that said "The number in this cell is formatted as text ...", and that warning was carried over into the pasted worksheet. We are using this template file to upload data into an asp.Net application, and because of this warning, the data for that cell was blank.

Is there any way to lock cell formatting so that when a user pastes data, it converts the data to numeric, or doesn't allow the data to paste, or something like that?

I want to allow for data to be pasted, but I want to enforce some kind of data typing on the pasted data??

Hi,

I had a quick look through some of the previous post and couldn't find a solution to my problem.

I want to apply Data Validation to a cell, so that only the following combination of letters and numbers can be entered.

Letter Letter Number Number Number Number Number Number Letter.
e.g AB123456C.

Any help would be greatly appreciated,
Thanks,
Dave

Hi all

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 ?

any help is greatly appreciated. Thanks

I have column of cells, both of with have drop down menus to select information from. I have an adjacent cell to display an error based on conditions. Is there a way to have conditional formatting to apply to the cells with Data Validation to change the color, based on the error. I have tried using the Conditional Formatting options but it still does not change the color of them. I believe i have to use the formula option, but not exactly sure what to put, i know a if statement could work but unsure on how, there are only two conditions that i would need for it to apply, if the adjacent cell has an error, and when it does not. Is this possible?

Hello,

I have a simple work book. Please refer to the attachment Book1.xls.

In B1, the data is limited (by Data Validation with a drop down List) to Yes and No.

I need to limit the range of data in D1 as, If B1 = Yes , then the value of D1 could be entered More or Equal to 51 ; and if B1 = No then the data entered into D1 should be Less than or Equals to 50

I prefer to do it with Data Validation. Please help.

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? Thanks in advance.

Edit: 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. Thanks again

I am creating a project tracking sheet. Each month I will enter a symbol to represent the status of my project. I have 4 symbols I want to use. I thought a data validation list with my four symbols would be best but it is not working. Any suggestion is appreciated. Please consider that I am new at this.

I can use a dynamic named range and a direct reference to the name and the DV works.

I can use a standard named range (not dynamic) and an INDIRECT() reference to a cell with the name of the range in it and the DV works.

But if I try to use a dynamic named range and an indirect reference to a cell with the name of the range in it, the DV fails.

Any workarounds out there? This project includes a LOT of DV lists, getting them to self-maintain is important if I can do it.

(Cross-posted)

Please help

I know of the formulae =sumif(a1:a21,"1 Bed",b1:b21). I would like to be able to use a data validation list for the "1 bed" section (so for instance there will be a list of 0 bed upto 5 bed) and then for the formulated cell to work out the rest when i select which number of bed property i want.

Does this make sense or shall i retype ?

Hi, I tried searching the forums for a solution to my problem, but didn't come across anything that looked similar.

I am trying to apply data validation to a column of cells using named ranges. However, each row has a unique associated named range. For example:

A_______B
Birds____*
Dogs____*
Cats____*

I can easily apply data validation to these three rows separately using named ranges.
Ie three separate named ranges:
=Birds
=Dogs
=Cats

However, I need a way to quickly apply data validation to column B using different named ranges for each row because there are about 2,000 rows. Is there a way to reference text in the cells of column A that contains the name of the named range? Or maybe a bit of VB code that could do it quickly?

Does anyone know a good solution to this problem?

Thanks!

I can't seem to find an example of this, so thought I'd post. I'm trying to do some data validation of a cell based on the value of another cell.

If cell A1 is No, then I want cell B1 to equal 0 only.

If cell A1 is Yes, then cell B1 can be any decimal value between 0 and 50, with a default of 3.

Can this be done easily enough with a custom data validation formula?

Thanks.

Is there any way that a symbol can be added to a data validation list? I have a coworker who would like to add a checkmark to a drop-down list.

Thank you!

Hi All,

In the shared workbook I wanted to protect format and contents of cell E4 where drop down list is given (Validation). all cells apart from E4 are locked so editing is not possible and my all formulas are safe within the worksheet.

Generally validation list not allowing to put any other contents but if I am copy pasting any other cell contents, it is allowing to paste.

If I am locking E4 then drop down is not working, means I can not select other values of drop down and if it is not locked then format of the cell can be change by pasting.

Is there any way to protect format of E4 while cell is unlocked along with only given list values.

Please find attached shared workbook for reference which is protected with blank password.

Thanks
Ganesh.

Hello!

You click a cell (A1)
The Data Validation dropdown comes up.
I don't see my item in the list, so I type the data ("dog")into A1.
I click off that cell.
If I click back to A1, now "dog" is in the dropdown and that new list will be saved when the workbook closes.


Can this be done?

VR/Lost

Hi all.
I am working on Excel 2007 but any solution to my problem needs to be compatible with earlier versions.

I have a cell containing data validation, pick from a list, the list containing just two options:
False, True

This works fine, provided that I select the required option from the dropdown list presented when the cell is selected. The value selected is then treated as the boolean values FALSE and TRUE respectively when formulae in other cells refer to this cell.

So far, so good.

My only problem is that it seems that I HAVE To select the desired value from the drop-down list. If I simply type False or True, then it fails validation. I have never observed this with other data validation from lists, although I am usually working on Excel 2003 and have not yet had a chance to test this under that environment.

Am I stuck with this?

Thanks

Is there anything like "format painter" for data validation?

Once I set up data validation for a cell, is it possible to copy those settings to another cell without having to do a cell copy & paste, which will also wipe out destination values and formatting?

When setting conditional formatting, it gets carried along when you do a format paint, but data validation settings are not considered part of formatting.

If VBA is the only way to do this, I can develop that solution on my own but I was hoping there was something built in that I was ignorant of.

Hi,

Is there a way to have a data validation drop down menu but at the same time allowing users to enter free text as well.

Is this possible ?

Thanks in advance !

is there any way that i can use a character to a cell as a data validation method.

for example.

i want to use AL and AN as the only characters that will accept as a value for A1

Hey,

I have a project I've been working on and I need help with something

I need to use Data Validation to not allow any Duplicate Values

In the attachment there is a spreadsheet to show what I'm looking for

Summary:
Inputted Values in F7 <> Values in range I7:Q9
If F7 = any value in the range, Data Validation suggests to change the value


If someone could help me on this I'd greatly appreciate it

Thank you sooo much for your time and efforts,
Bob

I hope someone can help me (or point me in the direction of an already solved query) before I plunge myself through the nearest window and into the apparently bottomless fountain in the quadrangle.

I am attempting to create a form in excel with dynamic data validation fields - the thing that is killing me is that I want to create multiple dependencies (ie, if you select something from a drop-down list in the first field, that then gives you a specific range in the second). What I can't seem to work out is the formula for making this work.

The format I'm following: if I select, for example, ACAT in the first list, I want that to then bring up the ACAT-dependent range in the drop-down list in the next cell. If I then select ACHE in the first list, I want that to bring up the ACHE-dependent range in the drop-down, and so on. I have named my ranges and column headings, it's just getting the formula to agree that I fail at.

Is this impossible? In an ideal world, I'd like to have one category list and two or three dependents, but seeing as I can't even get one to work...

I am trying to lock a data validation list so the user can ONLY select what is in the list and not remove the list. I know i can prevent text changes by enabling the Error Alert option but, if i am in that cell, i can still hit my DEL key and it will remove all data from that cell leaving it blank. How can i prevent that from happening?

Hello,

Is there a way to automatically reflect the changes done to existing List members in the cells that are data-validated against the List?

I have a couple Lists setup and if I add new entries, the drop downs in the data validated cells already show the updated members. However if I have selected an entry from the drop down and later I go and change the entry text in the drop down I still see the old value in the cells.

E.g. I select "John Doe" from a Resources list and at some point I change that entry for "Jane Doe". How would I make sure that all previously selected "John Doe"s are changed to "Jane Doe".

I read an incomplete answer somewhere that suggested not modifying or deleting List entries directly but from some sort of script. Does anybody have an example for doing this?

Thanks in advance!

I'm trying to use the worksheet_change event to trigger some changes on a
worksheet. I have a cell with values restricted to a list, using the Data,
Validation menu. Picking a cell value from this listbox does not appear to
trigger a change event.

What can I do to get round this problem? I don't want to use a separate
listbox control since, in addition to trying to trigger code from the listbox
value, it is also used in calculations using formulae in other cells.

Any help appreciated.

Steve