Hello all,
I have found and seen a lot of solutions for dependent data validation lists but I am looking for a
different approach.
I have a spreadsheet per employee to register their annual leave, extralegal holidays, etc .. (see attachment)
As you can see each type of holiday (the excel is in dutch but never mind that)
The employee Gerard has in this spreadsheet 12 days legal days, 1 compensation, 3 extra days (company wide) and 1 extra legal.
Since this may vary for each employe i am looking for a way to enter the codes in the calendar (range C13:AG24) with data validation.
Right now the list in the drop down box is a fixed list taken from a range (in tabblad Change Here!).
You may notice that if one selects a code the counters (in range B27:L40) are recalculated using straight formulas. This can be done using
whole days or half days (same code using H)
Here's my question:
Right now all available codes can still be selected from the drop down. I would like to have a drop down list with ONLY the holiday types
for which that person still has days left. That is if a counter reaches 0 this code should NOT be available for selection.
In this example code V+ is used up ... hence this code should be eliminated.
Can this be done ? Of course if half days is still available than only the code for half a day should be displayed (making the problem more
difficult).
Anyway ... any pointer is welcome.
Thankx a lot!!!
Just to be fair. ... crossposted this also in http://www.excelforum.com/excel-gen...wn-list-with-data-validation.html#post3734573
I have found and seen a lot of solutions for dependent data validation lists but I am looking for a
different approach.
I have a spreadsheet per employee to register their annual leave, extralegal holidays, etc .. (see attachment)
As you can see each type of holiday (the excel is in dutch but never mind that)
The employee Gerard has in this spreadsheet 12 days legal days, 1 compensation, 3 extra days (company wide) and 1 extra legal.
Since this may vary for each employe i am looking for a way to enter the codes in the calendar (range C13:AG24) with data validation.
Right now the list in the drop down box is a fixed list taken from a range (in tabblad Change Here!).
You may notice that if one selects a code the counters (in range B27:L40) are recalculated using straight formulas. This can be done using
whole days or half days (same code using H)
Here's my question:
Right now all available codes can still be selected from the drop down. I would like to have a drop down list with ONLY the holiday types
for which that person still has days left. That is if a counter reaches 0 this code should NOT be available for selection.
In this example code V+ is used up ... hence this code should be eliminated.
Can this be done ? Of course if half days is still available than only the code for half a day should be displayed (making the problem more
difficult).
Anyway ... any pointer is welcome.
Thankx a lot!!!
Just to be fair. ... crossposted this also in http://www.excelforum.com/excel-gen...wn-list-with-data-validation.html#post3734573