|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 03:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com