Validation Tool in Excel
 

Excel 2002, SP3

The Validation tool in Excel seems to be a bit defunctional. Like to know
if others has ran into this sort of issue or not and what work around has
been implimented if any?

Cell to be validated: N10

Type of Cell N10 is: Date

Validation Allow: Custom

Validation Formula:

=AND(WEEKDAY(N10,2)<6,N10S9,N10<S38)

If I use the above formula, it works fine, but if I use the below formula,
it allows any date into the cell

=AND(WEEKDAY(N10,2)<6,N10S9,N10<S38,ISERROR(MATCH (N10,HolidaySchedule,0)))

HolidaySchedule is the named range for between cells S6 and S38 with the
holiday dates all being calculated so as the holiday schedule doesn't have
to be manually adjusted annually. To allow for such thing, all 10 holidays
are calculated for the previous year (S9-S18, so when it's early in the
year, you still have the previous year's holiday schedule for reference),
the current year (S19 - S28), and the next year (S29-S38, so when it's late
in the year, you still have the next year's holiday schedule for reference).

Even though this formula works within a spreadsheet, the validation tool
doesn't like it, thus allows for any and all dates to be typed into the
cell. Not only does it happen with the ISERROR function, but it also
happens with using the < operator and it also happens using the ISNA
function.

If I had a relistic choice, I would use the Events within Excel, but Excel
doesn't have a true event mode that Access has with the BeforeChange Event.
Therefore, within Excel, if I was to attempt to program this sort of thing,
it would be a rather convoluted manner and I'm not sure if it's worth going
through that much effort or not. Excel does have a Change Event, but it's
only triggered after the change has taken place, not before, thus not
allowing to cancel out the change if the cell entry is invalid.

I'm not about to switch over to Access either cause Access has way too many
critical basic issues in it for a multi-user environment, which has rendered
that program useless for such an environment. That's not even talking about
the security aspect of Access, but rather just the basic data manipulation,
error checking process, and communication between the the client and the
server isn't working to a satisfactory level. This is not only true of
Access 97, but also of Access 2000, 2002, and 2003 as there is no real
difference between any of these 3 versions, as I still run into the same
issues with all 3 of these versions.

Sincerely,

Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000

Validation Tool in Excel
 

De-select ignore blanks and it will work

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Ronald Dodge" wrote in message
...
Excel 2002, SP3

The Validation tool in Excel seems to be a bit defunctional. Like to know
if others has ran into this sort of issue or not and what work around has
been implimented if any?

Cell to be validated: N10

Type of Cell N10 is: Date

Validation Allow: Custom

Validation Formula:

=AND(WEEKDAY(N10,2)<6,N10S9,N10<S38)

If I use the above formula, it works fine, but if I use the below formula,
it allows any date into the cell

=AND(WEEKDAY(N10,2)<6,N10S9,N10<S38,ISERROR(MATCH (N10,HolidaySchedule,0)))

HolidaySchedule is the named range for between cells S6 and S38 with the
holiday dates all being calculated so as the holiday schedule doesn't have
to be manually adjusted annually. To allow for such thing, all 10
holidays
are calculated for the previous year (S9-S18, so when it's early in the
year, you still have the previous year's holiday schedule for reference),
the current year (S19 - S28), and the next year (S29-S38, so when it's
late
in the year, you still have the next year's holiday schedule for
reference).

Even though this formula works within a spreadsheet, the validation tool
doesn't like it, thus allows for any and all dates to be typed into the
cell. Not only does it happen with the ISERROR function, but it also
happens with using the < operator and it also happens using the ISNA
function.

If I had a relistic choice, I would use the Events within Excel, but Excel
doesn't have a true event mode that Access has with the BeforeChange
Event.
Therefore, within Excel, if I was to attempt to program this sort of
thing,
it would be a rather convoluted manner and I'm not sure if it's worth
going
through that much effort or not. Excel does have a Change Event, but it's
only triggered after the change has taken place, not before, thus not
allowing to cancel out the change if the cell entry is invalid.

I'm not about to switch over to Access either cause Access has way too
many
critical basic issues in it for a multi-user environment, which has
rendered
that program useless for such an environment. That's not even talking
about
the security aspect of Access, but rather just the basic data
manipulation,
error checking process, and communication between the the client and the
server isn't working to a satisfactory level. This is not only true of
Access 97, but also of Access 2000, 2002, and 2003 as there is no real
difference between any of these 3 versions, as I still run into the same
issues with all 3 of these versions.

Sincerely,

Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000

Validation Tool in Excel
 

In this particular, it resolved my issue as I do have a formula in there,
but what about for those situations when a blank would be allowed, if you
didn't truely know?

--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000

"Peo Sjoblom" wrote in message
...
De-select ignore blanks and it will work

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Ronald Dodge" wrote in message
...
Excel 2002, SP3

The Validation tool in Excel seems to be a bit defunctional. Like to
know
if others has ran into this sort of issue or not and what work around
has
been implimented if any?

Cell to be validated: N10

Type of Cell N10 is: Date

Validation Allow: Custom

Validation Formula:

=AND(WEEKDAY(N10,2)<6,N10S9,N10<S38)

If I use the above formula, it works fine, but if I use the below
formula,
it allows any date into the cell


=AND(WEEKDAY(N10,2)<6,N10S9,N10<S38,ISERROR(MATCH (N10,HolidaySchedule,0)))

HolidaySchedule is the named range for between cells S6 and S38 with the
holiday dates all being calculated so as the holiday schedule doesn't
have
to be manually adjusted annually. To allow for such thing, all 10
holidays
are calculated for the previous year (S9-S18, so when it's early in the
year, you still have the previous year's holiday schedule for
reference),
the current year (S19 - S28), and the next year (S29-S38, so when it's
late
in the year, you still have the next year's holiday schedule for
reference).

Even though this formula works within a spreadsheet, the validation tool
doesn't like it, thus allows for any and all dates to be typed into the
cell. Not only does it happen with the ISERROR function, but it also
happens with using the < operator and it also happens using the ISNA
function.

If I had a relistic choice, I would use the Events within Excel, but
Excel
doesn't have a true event mode that Access has with the BeforeChange
Event.
Therefore, within Excel, if I was to attempt to program this sort of
thing,
it would be a rather convoluted manner and I'm not sure if it's worth
going
through that much effort or not. Excel does have a Change Event, but
it's
only triggered after the change has taken place, not before, thus not
allowing to cancel out the change if the cell entry is invalid.

I'm not about to switch over to Access either cause Access has way too
many
critical basic issues in it for a multi-user environment, which has
rendered
that program useless for such an environment. That's not even talking
about
the security aspect of Access, but rather just the basic data
manipulation,
error checking process, and communication between the the client and the
server isn't working to a satisfactory level. This is not only true of
Access 97, but also of Access 2000, 2002, and 2003 as there is no real
difference between any of these 3 versions, as I still run into the same
issues with all 3 of these versions.

Sincerely,

Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000

Validation Tool in Excel
 

Nevermind, could always hit the delete key or specify it in the formula.
Hitting the Delete key seems to bypass this validation process. Hrmmm, just
seem to keep finding ways to get around the issue, which then raises other
questions of how to prevent those things, if possible.

--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000

"Ronald Dodge" wrote in message
...
In this particular, it resolved my issue as I do have a formula in there,
but what about for those situations when a blank would be allowed, if you
didn't truely know?

--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000

"Peo Sjoblom" wrote in message
...
De-select ignore blanks and it will work

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Ronald Dodge" wrote in message
...
Excel 2002, SP3

The Validation tool in Excel seems to be a bit defunctional. Like to
know
if others has ran into this sort of issue or not and what work around
has
been implimented if any?

Cell to be validated: N10

Type of Cell N10 is: Date

Validation Allow: Custom

Validation Formula:

=AND(WEEKDAY(N10,2)<6,N10S9,N10<S38)

If I use the above formula, it works fine, but if I use the below
formula,
it allows any date into the cell



=AND(WEEKDAY(N10,2)<6,N10S9,N10<S38,ISERROR(MATCH (N10,HolidaySchedule,0)))

HolidaySchedule is the named range for between cells S6 and S38 with
the
holiday dates all being calculated so as the holiday schedule doesn't
have
to be manually adjusted annually. To allow for such thing, all 10
holidays
are calculated for the previous year (S9-S18, so when it's early in
the
year, you still have the previous year's holiday schedule for
reference),
the current year (S19 - S28), and the next year (S29-S38, so when it's
late
in the year, you still have the next year's holiday schedule for
reference).

Even though this formula works within a spreadsheet, the validation
tool
doesn't like it, thus allows for any and all dates to be typed into
the
cell. Not only does it happen with the ISERROR function, but it also
happens with using the < operator and it also happens using the ISNA
function.

If I had a relistic choice, I would use the Events within Excel, but
Excel
doesn't have a true event mode that Access has with the BeforeChange
Event.
Therefore, within Excel, if I was to attempt to program this sort of
thing,
it would be a rather convoluted manner and I'm not sure if it's worth
going
through that much effort or not. Excel does have a Change Event, but
it's
only triggered after the change has taken place, not before, thus not
allowing to cancel out the change if the cell entry is invalid.

I'm not about to switch over to Access either cause Access has way too
many
critical basic issues in it for a multi-user environment, which has
rendered
that program useless for such an environment. That's not even talking
about
the security aspect of Access, but rather just the basic data
manipulation,
error checking process, and communication between the the client and
the
server isn't working to a satisfactory level. This is not only true
of
Access 97, but also of Access 2000, 2002, and 2003 as there is no real
difference between any of these 3 versions, as I still run into the
same
issues with all 3 of these versions.

Sincerely,

Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000