TechnologyMangu
asked on
Dropdown with Dependency and Exclusion of Selection...
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).
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).
Another solution with no blanks between selections.
Exclude-from-list-no-blanks.xlsx
Exclude-from-list-no-blanks.xlsx
ASKER
How would I make it dependent on the managers? Is that with validation based on the managers name?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
If you are still having problems with implementation, please post your workbook including any attempts you have made to make the formulas work.
Brad
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,Sheet
Lists on Sheet2, selection on Sheet1.
Exclude-from-list.xlsx