Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
external usenet poster
 
Posts: 256
Default data validation invalid in dynamic validation list

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?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Validation for list in a different workbook vicky Excel Discussion (Misc queries) 2 November 6th 06 11:25 PM
update data validation list with new entries?? cjtj4700 Excel Discussion (Misc queries) 10 December 12th 05 02:00 AM
Help PLEASE! Not sure what answer is: Match? Index? Other? baz Excel Worksheet Functions 7 September 3rd 05 03:47 PM
Printing data validation scenarios SJC Excel Worksheet Functions 14 July 24th 05 12:43 AM
named range, data validation: list non-selected items, and new added items KR Excel Discussion (Misc queries) 1 June 24th 05 05:21 AM


All times are GMT +1. The time now is 10:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"