Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
LoveThatMouse
 
Posts: n/a
Default cell data not validated if navigating cell to cell with mouse

When skipping around in a table of data to update certain cells, its quite
common for some to click into the cell, enter the new data, and then click
the next cell where data must be updated. I have found that when Validating
data at these cells, validation does not work unless you hit the ENTER key to
accept the new data.

Is there a way to either have the validation work by using the mouse
navigation method or prevent mouse navigation from switching cells before an
ENTER key is pressed?
Thanks

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
davesexcel
 
Posts: n/a
Default cell data not validated if navigating cell to cell with mouse


hmm.. I can't duplicated the problem


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=543869

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
LoveThatMouse
 
Posts: n/a
Default cell data not validated if navigating cell to cell with mouse

I guess I did not define it far enough. If the validation criteria strickly
monitors the cell where data is being entered, you are correct and that works
fine. If, on the other hand, a custom formula is entered that checks another
cells value as a prerequisit for entry of a value in the Entry Cell, this is
where I run into the problem. My table consists of columns of entry that
represent manhours entry for a daily time sheet. At the bottom of the column
is a SUM cell for each day that totals all the cells above it in that table
column. The validation for each of the cells in a column checks the SUM cell
for a value =< 24. In other words, you can't have more than 24 hours in any
one day.

The first time I enter a value in any of that days column cells that makes
the SUM cell exceed 24, Excel actually accepts that entry if and only if I
just click into another field. This is even though the validation criteria
has been exceeded. If I try to enter something else in that same cell a
second time, nothing will be accepted using the Mouse Click method of entry
until I actually delete the contents of the field.

Using the ENTER key to make cell data entry has no such issues.
Any ideas?
Thanks
"davesexcel" wrote:


hmm.. I can't duplicated the problem


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=543869


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ragdyer
 
Posts: n/a
Default cell data not validated if navigating cell to cell with mouse

Say you're adding A1 to A10, with your Sum() function in A11:

=SUM(A1:A10)

And, I assume your validation formula might be:

=$A$11<=24

If I'm correct in my assumption of your formula, try this validation formula
instead:

=SUM(A1:A10)<=24

Which *doesn't* allow the entry you described, at least on my XL97 machine.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"LoveThatMouse" wrote in message
...
I guess I did not define it far enough. If the validation criteria

strickly
monitors the cell where data is being entered, you are correct and that

works
fine. If, on the other hand, a custom formula is entered that checks

another
cells value as a prerequisit for entry of a value in the Entry Cell, this

is
where I run into the problem. My table consists of columns of entry that
represent manhours entry for a daily time sheet. At the bottom of the

column
is a SUM cell for each day that totals all the cells above it in that

table
column. The validation for each of the cells in a column checks the SUM

cell
for a value =< 24. In other words, you can't have more than 24 hours in

any
one day.

The first time I enter a value in any of that days column cells that makes
the SUM cell exceed 24, Excel actually accepts that entry if and only if I
just click into another field. This is even though the validation criteria
has been exceeded. If I try to enter something else in that same cell a
second time, nothing will be accepted using the Mouse Click method of

entry
until I actually delete the contents of the field.

Using the ENTER key to make cell data entry has no such issues.
Any ideas?
Thanks
"davesexcel" wrote:


hmm.. I can't duplicated the problem


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile:

http://www.excelforum.com/member.php...o&userid=31708
View this thread:

http://www.excelforum.com/showthread...hreadid=543869



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ragdyer
 
Posts: n/a
Default cell data not validated if navigating cell to cell with mouse

Forgot the absolutes:

=SUM($B$1:$B$10)<=24

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
Say you're adding A1 to A10, with your Sum() function in A11:

=SUM(A1:A10)

And, I assume your validation formula might be:

=$A$11<=24

If I'm correct in my assumption of your formula, try this validation

formula
instead:

=SUM(A1:A10)<=24

Which *doesn't* allow the entry you described, at least on my XL97

machine.
--
HTH,

RD

--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------

-
"LoveThatMouse" wrote in message
...
I guess I did not define it far enough. If the validation criteria

strickly
monitors the cell where data is being entered, you are correct and that

works
fine. If, on the other hand, a custom formula is entered that checks

another
cells value as a prerequisit for entry of a value in the Entry Cell,

this
is
where I run into the problem. My table consists of columns of entry that
represent manhours entry for a daily time sheet. At the bottom of the

column
is a SUM cell for each day that totals all the cells above it in that

table
column. The validation for each of the cells in a column checks the SUM

cell
for a value =< 24. In other words, you can't have more than 24 hours in

any
one day.

The first time I enter a value in any of that days column cells that

makes
the SUM cell exceed 24, Excel actually accepts that entry if and only if

I
just click into another field. This is even though the validation

criteria
has been exceeded. If I try to enter something else in that same cell a
second time, nothing will be accepted using the Mouse Click method of

entry
until I actually delete the contents of the field.

Using the ENTER key to make cell data entry has no such issues.
Any ideas?
Thanks
"davesexcel" wrote:


hmm.. I can't duplicated the problem


--
davesexcel



------------------------------------------------------------------------
davesexcel's Profile:

http://www.excelforum.com/member.php...o&userid=31708
View this thread:

http://www.excelforum.com/showthread...hreadid=543869






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
LoveThatMouse
 
Posts: n/a
Default cell data not validated if navigating cell to cell with mouse

Right on the money. Thanks. Can't say I understand why one formula vs. the
other worked but your suggestion made my day. Thanks
LTM

"Ragdyer" wrote:

Forgot the absolutes:

=SUM($B$1:$B$10)<=24

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
Say you're adding A1 to A10, with your Sum() function in A11:

=SUM(A1:A10)

And, I assume your validation formula might be:

=$A$11<=24

If I'm correct in my assumption of your formula, try this validation

formula
instead:

=SUM(A1:A10)<=24

Which *doesn't* allow the entry you described, at least on my XL97

machine.
--
HTH,

RD

--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------

-
"LoveThatMouse" wrote in message
...
I guess I did not define it far enough. If the validation criteria

strickly
monitors the cell where data is being entered, you are correct and that

works
fine. If, on the other hand, a custom formula is entered that checks

another
cells value as a prerequisit for entry of a value in the Entry Cell,

this
is
where I run into the problem. My table consists of columns of entry that
represent manhours entry for a daily time sheet. At the bottom of the

column
is a SUM cell for each day that totals all the cells above it in that

table
column. The validation for each of the cells in a column checks the SUM

cell
for a value =< 24. In other words, you can't have more than 24 hours in

any
one day.

The first time I enter a value in any of that days column cells that

makes
the SUM cell exceed 24, Excel actually accepts that entry if and only if

I
just click into another field. This is even though the validation

criteria
has been exceeded. If I try to enter something else in that same cell a
second time, nothing will be accepted using the Mouse Click method of

entry
until I actually delete the contents of the field.

Using the ENTER key to make cell data entry has no such issues.
Any ideas?
Thanks
"davesexcel" wrote:


hmm.. I can't duplicated the problem


--
davesexcel



------------------------------------------------------------------------
davesexcel's Profile:

http://www.excelforum.com/member.php...o&userid=31708
View this thread:

http://www.excelforum.com/showthread...hreadid=543869





  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ragdyer
 
Posts: n/a
Default cell data not validated if navigating cell to cell with mouse

You're welcome -
And thank you for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"LoveThatMouse" wrote in message
...
Right on the money. Thanks. Can't say I understand why one formula vs. the
other worked but your suggestion made my day. Thanks
LTM

"Ragdyer" wrote:

Forgot the absolutes:

=SUM($B$1:$B$10)<=24

--
Regards,

RD


--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit

!

--------------------------------------------------------------------------

-
"Ragdyer" wrote in message
...
Say you're adding A1 to A10, with your Sum() function in A11:

=SUM(A1:A10)

And, I assume your validation formula might be:

=$A$11<=24

If I'm correct in my assumption of your formula, try this validation

formula
instead:

=SUM(A1:A10)<=24

Which *doesn't* allow the entry you described, at least on my XL97

machine.
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may

benefit !

--------------------------------------------------------------------------
-
"LoveThatMouse" wrote in

message
...
I guess I did not define it far enough. If the validation criteria
strickly
monitors the cell where data is being entered, you are correct and

that
works
fine. If, on the other hand, a custom formula is entered that

checks
another
cells value as a prerequisit for entry of a value in the Entry Cell,

this
is
where I run into the problem. My table consists of columns of entry

that
represent manhours entry for a daily time sheet. At the bottom of

the
column
is a SUM cell for each day that totals all the cells above it in

that
table
column. The validation for each of the cells in a column checks the

SUM
cell
for a value =< 24. In other words, you can't have more than 24

hours in
any
one day.

The first time I enter a value in any of that days column cells that

makes
the SUM cell exceed 24, Excel actually accepts that entry if and

only if
I
just click into another field. This is even though the validation

criteria
has been exceeded. If I try to enter something else in that same

cell a
second time, nothing will be accepted using the Mouse Click method

of
entry
until I actually delete the contents of the field.

Using the ENTER key to make cell data entry has no such issues.
Any ideas?
Thanks
"davesexcel" wrote:


hmm.. I can't duplicated the problem


--
davesexcel




------------------------------------------------------------------------
davesexcel's Profile:
http://www.excelforum.com/member.php...o&userid=31708
View this thread:
http://www.excelforum.com/showthread...hreadid=543869






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can I connect data in cell C to data in cell D? Angel82 Charts and Charting in Excel 2 March 8th 06 06:47 PM
ranking query JaimeTimbrell Excel Discussion (Misc queries) 2 February 16th 06 09:09 AM
Cell data format falloutx Excel Discussion (Misc queries) 1 February 10th 06 02:46 PM
Maximum data in cell Rachael Excel Discussion (Misc queries) 12 January 25th 06 06:46 PM
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 06:55 PM


All times are GMT +1. The time now is 05:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"