Data Validation That Feeds A Dependant Drop Down Menu

The attached is a sample of how you might configure a workbook.

The main formula used is the one creating the named ranges for employees available for each day:

=IFERROR(INDEX(Employees!$A$2:$E$20,SMALL(IF(Employees!B$2:B$20="A",ROW(Employees!$A$2:$A$20)-ROW(Employees!$A$2)+1,""),ROWS($1:1)),COLUMNS($A:$A)),"")

It is an array formula and must be entered with Ctrl+Shfit+Enter.

Looks a bit daunting but creates the list of employees with code A

The previous post pointed you a site where you can find an explanation of dependent drop-down lists: this technique is used in this sample.

To check you only select an employee once will require VBA coding - so leave that till later!

Hope this helps.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Thanks very much for your help guys, I now know what sort of Formula's I now need to go read up on. Thanks again.

Hi There,

Thanks again for your advice.

I'm not sure if I'm meant to start a new thread or continue developing the solution in this one - Hopefully I'm doing it correctly. I've been working on a sheet that starts off doing what I want (copy attached);

Synopsis of the attached Workbook

Tab 1 "Availability": This is where I mark which employees are available on which days of that week. I've set up a data validation drop down box on each cell which is also conditionally formatted to change colour for ease of review each week.

Tab 2 "Project Allocation": This is where I have a problem and could really do with some more assistance or a point in the right direction.

Column B "Monday" has a drop down box that's linked to VBA coding so that I can select multiple employees to work on Monday against one project. The coding also allows me to re-select a previously selected employee and it will remove them from that project.

Problems Requiring Solutions:

The four problems that I don't understand how to solve on Tab 2 are:

1: I need the drop down list for Monday to only allow me to select employees that are marked as available or provisionally available on Monday on Tab 1

2: I need the drop down box also to update as I allocate employees, for example if I use employee 6 on Monday project 1 they will no longer appear in the drop down box for any other project thereby making them impossible to double allocate.

3: I need same drop down box rules to appear on Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday and pull the relevant data as to employees availability from Tab 1

4: The final thing that would be good to have on Tab 2 would be some sort of conditionally formatted text colour so that Available employees are shown in green text and provisionally available employees are shown in Amber text.


Thanks again for anyone who takes the time to read and/or try to assist me in solving this problem.

I haven't looked at your file yet but see how much of your need is met by the attached:

The formulae used are as per previous postings.

Checks if employee allocated for a given day ...


=IFERROR(IF(MATCH(C2,Projects!$B$2:$B$16,0),"X",""),"")

Re-creates the dropdown list(s) as employees are allocated.


=IFERROR(INDEX(Tables!$C$2:$C$20,SMALL(IF(D$2:D$20<>"X",ROW($A$2:$A$20)-ROW($A$2)+1,""),ROWS($1:1)),COLUMNS($A:$A)),"")

Thanks for your e-mail John, I had a footer about with your excel doc and done a bit of reading up on IF Formula's to make some progress.

I'm now half way to where I want to be and have attached an updated sheet to better illustrate my remaining issues.

I have now included a table on tab 1 in Columns O - U that only pulls forward those employees that are marked as being available on the specific day. The first problem that I have is that my "Employee No.1" only shows on the table if two employees are selected and "Employee No.50" just doesn't seem to exist - I think its something to do with the last part of my formula but not sure.

Tab 2 then pulls uses data validation drop down lists that pull the data of available employees from the table I have created in columns O - U on tab 1. The problem that I have here is that I don't understand how to recode the VBA coding to allow Columns C - H to behave in the same way as the drop down box in column B?

The final query that then remains is how I make the drop down lists on tab 2 only allow me to use each employee once on each day (providing that they are available as per the table in tab 1)

At this stage, I'm considering just forgetting about having "provisionally" available employees as it looks like it could involve a seriously big formula.

Any help would be greatly appreciated.

I have done the following changes:

The selection formula in availability to (effectively) the version in my example :

=IFERROR(INDEX($A:$A,SMALL(IF(OFFSET($A$3:$A$52,0,MATCH(O$2,$B$2:$H$2,0))="Yes",ROW($A$3:$A$52),""),ROW()-2)),"")

The VBA code has a minor change:

Please Login or Register  to view this content.

from

Please Login or Register  to view this content.

so it works on columns B to H

For the last problem i.e. only allocate an employee once: the solution is the last workbook I sent you.

The columns DValMon (named ranges) etc are the "dynamic" dropdown lists which automatically change when an employee is assigned to a project.

You need a column to signify someone has been selected (the columns with "X" in them) and then re-generate the list omitting these entries.

All the formulae are in my workbook.

Thanks for sorting that VBA code john, its greatly appreciated.

I wonder if you can explain what it is that I am doing wrong when I try and replicate your formulas from the dependant drop down list page? I've tried messing around with it, but I just don't understand enough about excel to work it out as I don't really follow what it is that the formula is actually doing?

I've attached my best effort at the formula to see if you can spot my mistake?

I could not see anything wrong with your formula: I copied one of mine and changed the ranges and it worked !

=IFERROR(INDEX(Availability!$A$3:$H$52,SMALL(IF(Availability!B$3:B$52="Yes",ROW($A$3:$A$52)-ROW($A$3)+1,""),ROWS($1:1)),COLUMNS($A:$A)),"")

Attached has the updated "Table" tab.