R
Ronald Dodge
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,N10>S9,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,N10>S9,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
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,N10>S9,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,N10>S9,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