Solved

Dropdown with Dependency and Exclusion of Selection...

Posted on 2014-02-21
8
235 Views
Last Modified: 2014-02-23
I'm trying to see if there's a simple way to have a dropdown that a user can use to choose from a list, and when they move to any other row that has the same selection in the same column they can select any item in the list that hasn't been selected already.

I'm building a move list for people moving to new spaces at an office and would like a function that helps the different departments select the office numbers of each employee, and I'd like to send them a spreadsheet with all their names with a column for them to select office locations off a list, which generally looks like:

Office1a
Office1b
Office1c
Office1d
etc., etc., etc.,

I am going to use a dependent dropdown to give each manager a select choice of offices, but as they select offices in their list I don't want them to be able to select the same office for anyone else on their team so they do not put two people in the same office.

Is there a simple way to accomplish this without necessarily writing a function or macro (trying to avoid sending a macro notebook as people tend to hate those).
0
Comment
Question by:TechnologyMangu
  • 3
  • 3
  • 2
8 Comments
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 39879073
Try this.
With all office names in one list, and a formula in the list to select from, already used names will be blank.

=IF(ISERROR(MATCH(B2,Sheet1!B:B,0)),B2,"")

Lists on Sheet2, selection on Sheet1.
Exclude-from-list.xlsx
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 39879237
Another solution with no blanks between selections.
Exclude-from-list-no-blanks.xlsx
0
 

Author Comment

by:TechnologyMangu
ID: 39879516
How would I make it dependent on the managers? Is that with validation based on the managers name?
0
 
LVL 21

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 200 total points
ID: 39879693
A solution to first select manager for employee, then office depending on manager selection.
Exclude-from-list-with-managers.xlsx
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:TechnologyMangu
ID: 39880169
Last, but not least, how do I get rid of the extra spaces when offices are selected in the drop-down? Seems the list keeps the "empty" space of where an office existed in the list...
0
 
LVL 81

Accepted Solution

by:
byundt earned 300 total points
ID: 39880192
I modified hgholt's workbook to exclude the blanks in the data validation dropdowns.

To do so, I created a named range Offices, with a "Refers to" property of:
=Sheet2!$G$4:INDEX(Sheet2!$G$4:$G$1000,COUNTA(Sheet2!$C$4:$C$1000))

I then used the following array-entered (Control + Shift + Enter) formula in Sheet2 cell G4, copied down:
=IFERROR(INDEX(F$4:F$1000,SMALL(IF((F$4:F$1000<>"")*(C$4:C$1000=C4),ROW(F$4:F$1000)-ROW(F$4)+1,""),COUNTIF(C$4:C4,C4))),"")

And then the following data validation Source in Sheet1 cell D2:
=OFFSET(INDEX(Offices,MATCH(B2,INDEX(Office,,1),0)),0,0,COUNTIFS(INDEX(Office,,1),B2,Offices,"?*"))
Exclude-from-list-with-managersQ.xlsx
0
 

Author Comment

by:TechnologyMangu
ID: 39880213
Something is wrong when I try to use this sheet and the formulas in it - while yours appears to work it evaluates to errors in my file.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39880832
When you apply the data validation formula to a row with no manager chosen, you will get an error message that it evaluates to an error. If you ignore the error, it will work once you choose the manager.

If you are still having problems with implementation, please post your workbook including any attempts you have made to make the formulas work.

Brad
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel case statements 3 25
Excel VBA - reference a textbox on a userform using a variable dynamically 2 26
Excel - find text within text? 1 25
Delete Text 7 35
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now