Excel Data Validation (Time and List) query

[RandBlade]

Yes, yet another Excel query. Sorry but ages on Google still hasn't found an answer.

I'm using Data Validation to create a drop-down list that has the options "Yes" "No" and time in increments of 15 minutes, starting from 16:00 until 15:45. This refers to a list in a sheet Names!A2:A99 where A2="Yes", A3="No", A4=16:00, A5=16:15 etc

Of course Excel really records times as a fraction of a day. The time 16:00 gets shown in the formula bar on the top as 16:00:00
When I use the drop-down box in Data Validation if I select say 18:00 then it works, but if I type in 18:00 it rejects it as an invalid option. If I type in 1 then it recognises that (despite 1 not being on what I want the list to be) and displays it as 00:00 with the top formula line showing 01/01/1900 00:00:00

I can get the times to work using Data Validation->Time instead of ComboBox, but I want either limited words or a time to be allowed. Can't seem to get them to work together.

Any suggestions?

[Wraith]

Do you need them as times? It'd probably work if you just made the whole thing strings.

[RandBlade]

Well, if its a time then other formulas calculate off the times, though I suppose you could convert the strings to times.

Though if you were to type, it wouldn't recognise it as a string anyway would it? So that'd break it again.

[Wraith]

If you format the cells as text, it'll be strings no matter what you type. I assume this is a table, so you should only have to apply the formatting once, and it'll propogate to the rest of the rows as the table expands.

[RandBlade]

That appears to work, just need to re-convert the text to a number on the other side which is a bit of a shame. Stupid Excel, time is an issue dealt with really badly at times.

One more query while I have you, I'm trying to idiot-proof a spreadsheet but I deal with a lot of idiots. I've got a front-end where data is entered with a back-end lots of calcuations then some outputs. Problem is cutting and pasting in the front end breaks the formulae in the back. I've tried protecting the sheet but it still allows people to cut and paste even when its protected. Any non-macro way to turn off - for this sheet only - formula references moving when something is cut? Or stop people from using the cut function altogether on this sheet?

Only other work around I can think of is to have no direct references to the front-end and have Indirect() or Offset() references to it only instead of A14 etc.

[Wraith]

You should be able to use the protect sheet functionality to prevent cutting on a certain sheet. I don't know of anything for your other solution idea.