Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel cell doesn't except value

Status
Not open for further replies.

Jdubs

Technical User
Apr 3, 2003
11
GB
Hi wander if anyone can help.

I have an excel spreadsheet which has a cell into which I enter a date. This cell has data validation to make sure it is within a range. After typeing the date into the cell I want to navigate to another cell using my mouse, but this causes the validation rule to trigger.

However if I just either press enter, or use the tab button or the arrow keys I don't get the error.

Anyone have any ideas why this would be - is there a solution.
Cheers
dubs
 
Prior to getting the error, how are you choosing to "navigate" to another cell after entering the date?

Sawedoff

 
Hi Sawedoff

To help prevent user intervention all cell are protected apart from those which I want them to enter data into.
In testing situations it was possible to navigate between cells using the tab button, but it was found that most of the users wanted to try and navigate by clicking from cell to cell using the mouse.

All cells have some sort of data validation associated to them but the problem seems only to arise with those which are for dates.

Cheers
Jon
 

The ONLY way to terminate the edit process in a cell is [enter], [tab] or arrow keys...

OTHERWISE...

Excel assumes that you are continuing to edit. Notice in the FORMULA BAR, that when this happens, a REFERENCE is inserted by Excel.

Skip,

[glasses] [red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [bomb][tongue]
 
Thanks Skip, I had that about half typed (in another wording) but you said it better than I could.

Sawedoff

 
Hi,

I'm a bit confused (which isn't that strange for me)
If I open an excel spreadsheet and type a date in it
and then select another cell there doesn't seem to be a problem and I haven't had to press enter or use navigation keys
 

Jdubs,

When you go in to see the doctor, he/she asks questions like, "where does it hurt?"

After collecting the data, he/she makes a diagnosis.

In this case, the data indicaed that there might have been entered as a formula, in which case using the mouse to make another selection, would INSERT that reference in the cell being edited, causing the verification to trigger.

Skip,

[glasses] [red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [bomb][tongue]
 
Skip said:
The ONLY way to terminate the edit process in a cell is [enter], or arrow keys...
And [ignore][Tab][/ignore]. But I suspect that is why there is a big space in your post - you didn't use [ignore] tags did you? I nearly did the same thing, but luckily previewed first.[tongue]

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.
 

John,

That's EXACTLY what happened!

Skip,

[glasses] [red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [bomb][tongue]
 
what are the date spans that you are trying to validate?
what date are you entering?
what FORMAT is the cell set to?
what FORMAT have you set the date span validation to?

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top