I
ilia
Hi everyone,
I'm running into a strange issue, and I cannot figure out a solution.
I have a dictator-style spreadsheet for data entry, where each field is
validated based on a list. This is an attendance data sheet, where the
number of hours must equal to 8, in whole hours. Each day has this
structure of rows, labeled in column C:
Present
Doctor
Holiday
Sick
Family
Vacation
Weather
Using the first weekday as example: the top row (Present at E7) has the
following formula:
=IF(MONTH(cellAbove)=SelectedMonthNumber,8-SUM(E8:E13),"")
....where cellAbove is actually the date, SelectedMonthNumber is a
calculated field based on another validation list of months.
The second row (Doctor at E8) has the following validation rule of list
type:
=IF(E8<1,OFFSET($A$6,0,0,9-(SUM(E$8:E$13)),1),OFFSET($A$6,0,0,MAX(9-(SUM(E$8:E$13)),E8)))
Cells A6 through A14 contain numbers from 0 to 8. The other rows are
structured similarly except with respectively different relative
references (e.g. E8, E9, etc)
This provides for the following functionality:
* If no hours in any other category are entered, each category will be
able to select from up to 8 hours (e.g. doctor for 2 hours, the formula
in E7 will read 6)
* If another category has hours, the selection for each other category
will allow up to the number of hours to make up 8 (e.g. if doctor has
2, Family will have up to 6)
* If a category is changed, any value up to the current number of
hours is allowed (hence the IF/MAX combination).
This works perfectly if the number of hours is selected from the
in-cell dropdown. However, if the number of hours is entered by hand
and is more than 4, the validation range changes before the cell is
validated, thus resulting in an error. For example, if I type in 5 in
cell E8 (Doctor) I get a validation error because at this point the
validation list becomes $A$6:$A$10. This does not occur when the value
is selected from the dropdown.
Does anyone know of a workaround for this kind of issue? I'm also
using a VBA hook for SheetChange event (to update the hidden data sheet
whenever a change is made), is it possible to catch it in there
somewhere?
I'm running into a strange issue, and I cannot figure out a solution.
I have a dictator-style spreadsheet for data entry, where each field is
validated based on a list. This is an attendance data sheet, where the
number of hours must equal to 8, in whole hours. Each day has this
structure of rows, labeled in column C:
Present
Doctor
Holiday
Sick
Family
Vacation
Weather
Using the first weekday as example: the top row (Present at E7) has the
following formula:
=IF(MONTH(cellAbove)=SelectedMonthNumber,8-SUM(E8:E13),"")
....where cellAbove is actually the date, SelectedMonthNumber is a
calculated field based on another validation list of months.
The second row (Doctor at E8) has the following validation rule of list
type:
=IF(E8<1,OFFSET($A$6,0,0,9-(SUM(E$8:E$13)),1),OFFSET($A$6,0,0,MAX(9-(SUM(E$8:E$13)),E8)))
Cells A6 through A14 contain numbers from 0 to 8. The other rows are
structured similarly except with respectively different relative
references (e.g. E8, E9, etc)
This provides for the following functionality:
* If no hours in any other category are entered, each category will be
able to select from up to 8 hours (e.g. doctor for 2 hours, the formula
in E7 will read 6)
* If another category has hours, the selection for each other category
will allow up to the number of hours to make up 8 (e.g. if doctor has
2, Family will have up to 6)
* If a category is changed, any value up to the current number of
hours is allowed (hence the IF/MAX combination).
This works perfectly if the number of hours is selected from the
in-cell dropdown. However, if the number of hours is entered by hand
and is more than 4, the validation range changes before the cell is
validated, thus resulting in an error. For example, if I type in 5 in
cell E8 (Doctor) I get a validation error because at this point the
validation list becomes $A$6:$A$10. This does not occur when the value
is selected from the dropdown.
Does anyone know of a workaround for this kind of issue? I'm also
using a VBA hook for SheetChange event (to update the hidden data sheet
whenever a change is made), is it possible to catch it in there
somewhere?