Data Validation allowing invalid entries

**Simon1185** · 10-14-2010, 09:42 AM

Hello All,

Up to now I've not had much use for using the data validation function, but I am now using it to limit entries in a range of cells being allowed to take another cell's calculation below zero. The forumla applied to these cells is:

Please Login or Register  to view this content.

E17 contains the remaining hours available to be scheduled in a workshop. As hours are entered into the range of cells under the data validation they are subtracted from E17. This has worked to a point but I find myself in certain situations able to enter figures to take E17 below zero.

Due to the preconceived layout of the spreadsheet the range of cells that the data validation is applied to is not one single area, i.e. it is something like O17:22, S17:S22, W17:22, etc. My first suspicions are that this is where the problem springs from; the minus can be achieved by the first value entered into any of those column ranges, after which a second value added will correctly refuse the entry.

I have been careful to ensure I have selected all the right cells, have ticked “apply changes to all cells with these settings” when making changes and have tried both include and ignore blank cells.

Any ideas anyone?

Many thanks in advance for your help.

Simon.

**NBVC** · 10-14-2010, 09:47 AM

Can we see a sample (remove confidetial info) of your workbook telling us in what situations exactly the problem occurs?

**ChemistB** · 10-14-2010, 09:59 AM

See attachment.
I selected your range(s) and used custom =$E17>0

It seems to work fine.

**Simon1185** · 10-14-2010, 12:11 PM

Hi guys,

See attached. I tried the simplier formula and thought I had it sussed, but see you are able to enter any figure in a 'paintshop' slot without seeing an error. ChemistB my sheet is slightly different in that I don't have a 'allowance' for each row, but instead I have made both the column and row references absolute in the formula, so this shouldn't be the problem.

To give you a specific example, I can enter 79hrs in BL3 with only 75.4hrs available.

Right before I uploaded the attachment I have noticed that this only seems possible if I make an entry and then click into another cell. If I hit return instead I get an error message as I should.

Thanks for your prompt responses.

**Simon1185** · 10-18-2010, 06:02 AM

Bump (thank you, please!)

**ChemistB** · 10-18-2010, 09:44 AM

I think if you click outside the cell rather than using Enter, the Data Validation capabilities do not work when using a custom formula. :-/ I played around with it a little and haven't come up with a workaround yet.

**Simon1185** · 10-19-2010, 05:29 AM

Is this something you are still pondering? Or do you think it's an Excel quirk we won't be able to get around?

Now we seem to have pin-pointed a more specific problem should I maybe start a new thread to ask if anyone knows how to force data entry by hitting return rather than clicking away from a cell?

I read along similar lines this morning about disabling the fill handle and drag-and-drop functions, but it's not quite the same and wouldn't help I don't think.

Thanks,

Simon.