Data validation not working

In the ALLOW, instead of "CUSTOM", change to "LIST".

In Data validation select list, and then enter the name in the list box.

PATSYS,

Thanks for your helpful suggestion. Yes, that only allows for one entry to be entered.

However I don't want it to display a drop down arrow (the user shouldn;t feel there are more entries, and also because what I was trying should work in principle).

Any ideas to correct or explain why the custom formula is not being validated.

Regards

I don't know why it didn't work, but you can deselect the drop down with the tick box

Why do you want the user to enter this value, if there is only one choice ?
The formula =FIN_year should work fine.
It seems to me that what you want is to protect this formula from beeing changed by the user. Then you should use cell protection, not datavalidation.

exceluser2007 said:

PATSYS,

However I don't want it to display a drop down arrow (the user shouldn;t feel there are more entries, and also because what I was trying should work in principle).

Regards

Click to expand...

If you don't want the arrow to appear, uncheck the In-cell dropdown checkbox in the SETTINGS tab of the Data Validation window.

Hi PATSYS, mike2008, Bjornar,

Thanks you for your great tips!

I didn't know about preventing the drop down list functionality, works well for a single value.

Bjornar, I looked into the cell protection functionality as you suggested. It is quite helpful, but requires the entire worksheet to be protected first (to my understanding). The option above seems easier for a handful of cells to protect.

Again thankyou all for your help.

Using datavalidation will not prevent the user for replacing your formula with the value '2008'. I guess you have a reason for using a formula in this cell, and not just put in the value '2008'. Pasting something on a cell with validation, will also wipe out the validation.

Worksheet protection is great, for preventing users from changing cells containing formulas. As default all cells is locked, so what you do is to unlock only the cells that you want the user to enter values in. Keep all other cells locked. So when you use worksheet protection, dont try to figure out which cells too lock. Instead figure out what cells to leave unlocked, and leave all other cells locked.

In Excel 2002 and newer version you can choose if you want the user to be able to select locked cells or not. If you choose not to let them select locked cells, this will make the navigation easier for the user, as they now can TAB thru the cells they are supposed to enter values in. When the user enters a value, and press ENTER, Excel jumps to the next unlocked cell.
If you want them to be able to copy from locked cells (but not change them) you must allow them to select locked cell.

In most cases I don't bother with entering a password for protection the sheet. If the user wants to unprotect the sheet, he probably has a good reason to do this, and knows what he is doing. A password can easily be broken too.

Also cross posted here!