Exclude codes with Data validation

[tim]

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-gene...ml#post3734573

[bgoree09]

Good afternoon,

Please see the attached file. This may not be exactly what you are looking for (and I only speak English, so I apologize for that), but hopefully this helps. I've added a couple columns off to the right on the Gerard sheet (the text is red). The first column is a helper that determines if there is any remaining time in that category, and gives a unique number to each line (the row function works nicely for this). There was some merging with the list, so there are actually two different formulas that alternate in order to make a good list. The second column returns each unique category in order. The breakdown of that formula is something like:

Index - the answer array

small - finds the smallest value in the unique array ** it looks something like small(--Array--, row() - 26). It just so happens that I started in row #27, but I want it to show the SMALLEST value and then have row #28 show the second smallest, etc**

match - defines the correct row for the answer array

iferror - anything without an answer returns a blank instead of #NUM.

I then changed the data validation list to my unique category list, which is dynamic depending on the amount of time used.

Again, I apologize if I am not understanding, but hopefully this will put you in the right direction.

Best of luck,

[tim]

Hey bgoree09,

You should not apologize ... you understood perfectly what my question was all about.
Your solution certainly helps ... didn't think about using helper columns.
Thankx a lot!

Good afternoon,

Please see the attached file. This may not be exactly what you are looking for (and I only speak English, so I apologize for that), but hopefully this helps. I've added a couple columns off to the right on the Gerard sheet (the text is red). The first column is a helper that determines if there is any remaining time in that category, and gives a unique number to each line (the row function works nicely for this). There was some merging with the list, so there are actually two different formulas that alternate in order to make a good list. The second column returns each unique category in order. The breakdown of that formula is something like:

Index - the answer array

small - finds the smallest value in the unique array ** it looks something like small(--Array--, row() - 26). It just so happens that I started in row #27, but I want it to show the SMALLEST value and then have row #28 show the second smallest, etc**

match - defines the correct row for the answer array

iferror - anything without an answer returns a blank instead of #NUM.

I then changed the data validation list to my unique category list, which is dynamic depending on the amount of time used.

Again, I apologize if I am not understanding, but hopefully this will put you in the right direction.

Best of luck,