software

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
hmm.. I can't duplicated the problem--
davesexcel------------------------------------------------------------------------
davesexcel's Profile: www.excelforum.com/member.php...oamp;userid=31708
View this thread: www.excelforum.com/showthread...hreadid=543869I 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 =lt; 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
quot;davesexcelquot; wrote:

gt;
gt; hmm.. I can't duplicated the problem
gt;
gt;
gt; --
gt; davesexcel
gt;
gt;
gt; ------------------------------------------------------------------------
gt; davesexcel's Profile: www.excelforum.com/member.php...oamp;userid=31708
gt; View this thread: www.excelforum.com/showthread...hreadid=543869
gt;
gt;

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$11lt;=24

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

=SUM(A1:A10)lt;=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 !
---------------------------------------------------------------------------
quot;LoveThatMousequot; gt; wrote in message
...
gt; I guess I did not define it far enough. If the validation criteria
strickly
gt; monitors the cell where data is being entered, you are correct and that
works
gt; fine. If, on the other hand, a custom formula is entered that checks
another
gt; cells value as a prerequisit for entry of a value in the Entry Cell, this
is
gt; where I run into the problem. My table consists of columns of entry that
gt; represent manhours entry for a daily time sheet. At the bottom of the
column
gt; is a SUM cell for each day that totals all the cells above it in that
table
gt; column. The validation for each of the cells in a column checks the SUM
cell
gt; for a value =lt; 24. In other words, you can't have more than 24 hours in
any
gt; one day.
gt;
gt; The first time I enter a value in any of that days column cells that makes
gt; the SUM cell exceed 24, Excel actually accepts that entry if and only if I
gt; just click into another field. This is even though the validation criteria
gt; has been exceeded. If I try to enter something else in that same cell a
gt; second time, nothing will be accepted using the Mouse Click method of
entry
gt; until I actually delete the contents of the field.
gt;
gt; Using the ENTER key to make cell data entry has no such issues.
gt; Any ideas?
gt; Thanks
gt; quot;davesexcelquot; wrote:
gt;
gt; gt;
gt; gt; hmm.. I can't duplicated the problem
gt; gt;
gt; gt;
gt; gt; --
gt; gt; davesexcel
gt; gt;
gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; davesexcel's Profile:
www.excelforum.com/member.php...oamp;userid=31708
gt; gt; View this thread:
www.excelforum.com/showthread...hreadid=543869
gt; gt;
gt; gt;Forgot the absolutes:

=SUM($B$1:$B$10)lt;=24

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
quot;Ragdyerquot; gt; wrote in message
...
gt; Say you're adding A1 to A10, with your Sum() function in A11:
gt;
gt; =SUM(A1:A10)
gt;
gt; And, I assume your validation formula might be:
gt;
gt; =$A$11lt;=24
gt;
gt; If I'm correct in my assumption of your formula, try this validation
formula
gt; instead:
gt;
gt; =SUM(A1:A10)lt;=24
gt;
gt; Which *doesn't* allow the entry you described, at least on my XL97
machine.
gt; --
gt; HTH,
gt;
gt; RD
gt;
gt; --------------------------------------------------------------------------
-
gt; Please keep all correspondence within the NewsGroup, so all may benefit !
gt; --------------------------------------------------------------------------
-
gt; quot;LoveThatMousequot; gt; wrote in message
gt; ...
gt; gt; I guess I did not define it far enough. If the validation criteria
gt; strickly
gt; gt; monitors the cell where data is being entered, you are correct and that
gt; works
gt; gt; fine. If, on the other hand, a custom formula is entered that checks
gt; another
gt; gt; cells value as a prerequisit for entry of a value in the Entry Cell,
this
gt; is
gt; gt; where I run into the problem. My table consists of columns of entry that
gt; gt; represent manhours entry for a daily time sheet. At the bottom of the
gt; column
gt; gt; is a SUM cell for each day that totals all the cells above it in that
gt; table
gt; gt; column. The validation for each of the cells in a column checks the SUM
gt; cell
gt; gt; for a value =lt; 24. In other words, you can't have more than 24 hours in
gt; any
gt; gt; one day.
gt; gt;
gt; gt; The first time I enter a value in any of that days column cells that
makes
gt; gt; the SUM cell exceed 24, Excel actually accepts that entry if and only if
I
gt; gt; just click into another field. This is even though the validation
criteria
gt; gt; has been exceeded. If I try to enter something else in that same cell a
gt; gt; second time, nothing will be accepted using the Mouse Click method of
gt; entry
gt; gt; until I actually delete the contents of the field.
gt; gt;
gt; gt; Using the ENTER key to make cell data entry has no such issues.
gt; gt; Any ideas?
gt; gt; Thanks
gt; gt; quot;davesexcelquot; wrote:
gt; gt;
gt; gt; gt;
gt; gt; gt; hmm.. I can't duplicated the problem
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; davesexcel
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; davesexcel's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31708
gt; gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=543869
gt; gt; gt;
gt; gt; gt;
gt;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

quot;Ragdyerquot; wrote:

gt; Forgot the absolutes:
gt;
gt; =SUM($B$1:$B$10)lt;=24
gt;
gt; --
gt; Regards,
gt;
gt; RD
gt;
gt; ---------------------------------------------------------------------------
gt; Please keep all correspondence within the NewsGroup, so all may benefit !
gt; ---------------------------------------------------------------------------
gt; quot;Ragdyerquot; gt; wrote in message
gt; ...
gt; gt; Say you're adding A1 to A10, with your Sum() function in A11:
gt; gt;
gt; gt; =SUM(A1:A10)
gt; gt;
gt; gt; And, I assume your validation formula might be:
gt; gt;
gt; gt; =$A$11lt;=24
gt; gt;
gt; gt; If I'm correct in my assumption of your formula, try this validation
gt; formula
gt; gt; instead:
gt; gt;
gt; gt; =SUM(A1:A10)lt;=24
gt; gt;
gt; gt; Which *doesn't* allow the entry you described, at least on my XL97
gt; machine.
gt; gt; --
gt; gt; HTH,
gt; gt;
gt; gt; RD
gt; gt;
gt; gt; --------------------------------------------------------------------------
gt; -
gt; gt; Please keep all correspondence within the NewsGroup, so all may benefit !
gt; gt; --------------------------------------------------------------------------
gt; -
gt; gt; quot;LoveThatMousequot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I guess I did not define it far enough. If the validation criteria
gt; gt; strickly
gt; gt; gt; monitors the cell where data is being entered, you are correct and that
gt; gt; works
gt; gt; gt; fine. If, on the other hand, a custom formula is entered that checks
gt; gt; another
gt; gt; gt; cells value as a prerequisit for entry of a value in the Entry Cell,
gt; this
gt; gt; is
gt; gt; gt; where I run into the problem. My table consists of columns of entry that
gt; gt; gt; represent manhours entry for a daily time sheet. At the bottom of the
gt; gt; column
gt; gt; gt; is a SUM cell for each day that totals all the cells above it in that
gt; gt; table
gt; gt; gt; column. The validation for each of the cells in a column checks the SUM
gt; gt; cell
gt; gt; gt; for a value =lt; 24. In other words, you can't have more than 24 hours in
gt; gt; any
gt; gt; gt; one day.
gt; gt; gt;
gt; gt; gt; The first time I enter a value in any of that days column cells that
gt; makes
gt; gt; gt; the SUM cell exceed 24, Excel actually accepts that entry if and only if
gt; I
gt; gt; gt; just click into another field. This is even though the validation
gt; criteria
gt; gt; gt; has been exceeded. If I try to enter something else in that same cell a
gt; gt; gt; second time, nothing will be accepted using the Mouse Click method of
gt; gt; entry
gt; gt; gt; until I actually delete the contents of the field.
gt; gt; gt;
gt; gt; gt; Using the ENTER key to make cell data entry has no such issues.
gt; gt; gt; Any ideas?
gt; gt; gt; Thanks
gt; gt; gt; quot;davesexcelquot; wrote:
gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; hmm.. I can't duplicated the problem
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; davesexcel
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; davesexcel's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=31708
gt; gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=543869
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt;
gt;

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
quot;LoveThatMousequot; gt; wrote in message
...
gt; Right on the money. Thanks. Can't say I understand why one formula vs. the
gt; other worked but your suggestion made my day. Thanks
gt; LTM
gt;
gt; quot;Ragdyerquot; wrote:
gt;
gt; gt; Forgot the absolutes:
gt; gt;
gt; gt; =SUM($B$1:$B$10)lt;=24
gt; gt;
gt; gt; --
gt; gt; Regards,
gt; gt;
gt; gt; RD
gt; gt;
gt;
gt; --------------------------------------------------------------------------
-
gt; gt; Please keep all correspondence within the NewsGroup, so all may benefit
!
gt;
gt; --------------------------------------------------------------------------
-
gt; gt; quot;Ragdyerquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Say you're adding A1 to A10, with your Sum() function in A11:
gt; gt; gt;
gt; gt; gt; =SUM(A1:A10)
gt; gt; gt;
gt; gt; gt; And, I assume your validation formula might be:
gt; gt; gt;
gt; gt; gt; =$A$11lt;=24
gt; gt; gt;
gt; gt; gt; If I'm correct in my assumption of your formula, try this validation
gt; gt; formula
gt; gt; gt; instead:
gt; gt; gt;
gt; gt; gt; =SUM(A1:A10)lt;=24
gt; gt; gt;
gt; gt; gt; Which *doesn't* allow the entry you described, at least on my XL97
gt; gt; machine.
gt; gt; gt; --
gt; gt; gt; HTH,
gt; gt; gt;
gt; gt; gt; RD
gt; gt; gt;
gt; gt;
gt; --------------------------------------------------------------------------
gt; gt; -
gt; gt; gt; Please keep all correspondence within the NewsGroup, so all may
benefit !
gt; gt;
gt; --------------------------------------------------------------------------
gt; gt; -
gt; gt; gt; quot;LoveThatMousequot; gt; wrote in
message
gt; gt; gt; ...
gt; gt; gt; gt; I guess I did not define it far enough. If the validation criteria
gt; gt; gt; strickly
gt; gt; gt; gt; monitors the cell where data is being entered, you are correct and
that
gt; gt; gt; works
gt; gt; gt; gt; fine. If, on the other hand, a custom formula is entered that
checks
gt; gt; gt; another
gt; gt; gt; gt; cells value as a prerequisit for entry of a value in the Entry Cell,
gt; gt; this
gt; gt; gt; is
gt; gt; gt; gt; where I run into the problem. My table consists of columns of entry
that
gt; gt; gt; gt; represent manhours entry for a daily time sheet. At the bottom of
the
gt; gt; gt; column
gt; gt; gt; gt; is a SUM cell for each day that totals all the cells above it in
that
gt; gt; gt; table
gt; gt; gt; gt; column. The validation for each of the cells in a column checks the
SUM
gt; gt; gt; cell
gt; gt; gt; gt; for a value =lt; 24. In other words, you can't have more than 24
hours in
gt; gt; gt; any
gt; gt; gt; gt; one day.
gt; gt; gt; gt;
gt; gt; gt; gt; The first time I enter a value in any of that days column cells that
gt; gt; makes
gt; gt; gt; gt; the SUM cell exceed 24, Excel actually accepts that entry if and
only if
gt; gt; I
gt; gt; gt; gt; just click into another field. This is even though the validation
gt; gt; criteria
gt; gt; gt; gt; has been exceeded. If I try to enter something else in that same
cell a
gt; gt; gt; gt; second time, nothing will be accepted using the Mouse Click method
of
gt; gt; gt; entry
gt; gt; gt; gt; until I actually delete the contents of the field.
gt; gt; gt; gt;
gt; gt; gt; gt; Using the ENTER key to make cell data entry has no such issues.
gt; gt; gt; gt; Any ideas?
gt; gt; gt; gt; Thanks
gt; gt; gt; gt; quot;davesexcelquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; hmm.. I can't duplicated the problem
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; davesexcel
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; gt; gt; davesexcel's Profile:
gt; gt; gt; www.excelforum.com/member.php...oamp;userid=31708
gt; gt; gt; gt; gt; View this thread:
gt; gt; gt; www.excelforum.com/showthread...hreadid=543869
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;