Forum Discussion
Drop down list "moving" selection when selected in another cell
I am rather new to excel but have been able to figure out most things with a quick google search. However, this one thing is giving me trouble.
Here's what I'm trying to achieve:
I have a spreadsheet with different zones I can assign my employees to.
Each zone has cells that are linked to a table with my employee's names and I can select them via drop down menu.
When I want to move an employee to a different zone, I would like to be able to select their name in a different cell under the new zone and have it automatically reset the old cell containing their name to a blank cell.
Everything I see is just to make it so they can only be selected once, but this would eliminate them from the drop down menu as I assign them.
That means when I want to "move" them I'd have to reset their current cell to blank manually before being able to select them elsewhere.
I want the drop down list to include all of the names all the time but for it to reset the old cell to blank when I select them elsewhere.
Is this possible?
Please help!
Does the VBA code in the attached file work as intended? If you select a name from a dropdown in a cell in range F5:F24 the code checks if this name is found in range E5:E24. If it is found in E5:E24 then it gets cleared. The corresponding result is returned for a name selected from a dropdown in a cell in range E5:E24. The ranges in the VBA code can be changed according to the actual size of the "zones".
Is it possible for you to post a facsimile (no real names or other identifiable realities) of your spreadsheet. It's hard to follow what you're describing from your verbal description alone.
Post that facsimile on OneDrive or GoogleDrive with a link here that grants access to it. Not an image; an actual working spreadsheet.
mathetes The place I work for doesn't allow me to share links to spreadsheets I create at work. When I go to share the link and manage access, the "anyone" option is greyed out and it says my company is preventing me from selecting this option. Since I don't have a PC or excel at home, pictures are the best I can do. Hope this helps explain it.
So let's say I have my list of employees and right now I have these employees working in A zone and B zone. But I need to move Amy to B zone.
I would like to be able to select Amy in the drop down under B zone. Because I want it this way, drop down lists that exclude previously selected people doesn't sound appealing for my workflow.
When I do select Amy in B zone, I would like to set up a rule or formula that would automatically reset the old cell Amy was listed in (under A zone) to be blank.
As it stands I have to remember to delete an employee from their old location before or after moving them.
I have thought of setting up a rule to highlight duplicates but that still means I have to go and "blank" the cell manually.
Is there a way to have it do it automatically when I "move" someone?
The pictures help explain to a certain extent. Unfortunately, they don't explain enough. Are there ways to do what you describe? There are no doubt multiple ways and at the end of this note I give one example.
HOWEVER, to really do this in a completely professional way, I would want to know a LOT more about your entire data structure. [For background here, I was at one point the director of a major corporation's HR and Payroll database (I retired over 20 years ago), so have all kinds of experience working with data like this.] With that background, I'm assuming that there is data about Amy that extends to far more than simply the locations where she works; such things as job title, pay rates, employment date, etc., etc. And HOW all that data is organized would have a lot to do with how to best handle this matter of assigning a location and displaying each employee's current place of work.
That said, it may be that using drop down lists under A and B to fill in name and locations just isn't the most effective way to do this.
I'm attaching an example of a slightly different approach that differentiates between input and output. This uses the FILTER function which does require a relatively new version of Excel or a subscription to Microsoft 365.
