Drop down list "moving" selection when selected in another cell

[sophiemarie92]

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!

[#2]

@sophiemarie92 

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.

[#3]

@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?

[#4]

@sophiemarie92 

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.

[#5]

@mathetes

Thanks for the response!

So here’s some context, this is just being used to track which areas my associates are working in at any given moment. So no additional information about each employee, other than names, really needs to be included. The employees get assigned and move zones regularly each day.

The point is for it to act as a visual aid and update in real time if me or my leads tell an associate to work in another zone. Throughout the day work comes in to the various “zones” and sometimes one zone gets higher volume of work and we need to flex people to that zone. After telling the employee “go work in B zone” we can then update the spreadsheet and all see it in real time. That way we are aligned if anyone comes up and asks questions or forgot where they are supposed to be. 

The reason I have it set up the way I do is mostly because it needs to be very easy to use for my leads who are not very computer proficient, let alone with excel. I’m trying to keep it looking simple and lay out the zones in the order that they appear within our warehouse. I even hide the column that contains the list of names to avoid confusion and allow for less cluttered printing for them. 

Another reason is that each zone can only hold a certain number of employees. So I have made the different zones contain different number of cells to signify the headcount capacity.

Basically a lot just to say that I still think the way I’m envisioning it working, while probably harder to set up, would be better for my team from a visual and ease of use standpoint. 

[#6]

@sophiemarie92 

You gave a lot of context, which was helpful.

What I didn't see was any reference to the solution I gave you, which does work, work simply, and (unless I'm missing something) would work in the context of what you describe. Did you even open it?

[#7]

Yes I opened it. I just don’t want the actual work flow of moving someone being in a table to the side like that. I prefer the cleaner look of just the “zones” being visible and hiding the table with the names to make it easier for them to glance at and print off. So while your solution does technically work it’s not exactly an answer to my original question. I’m looking for a way to make it work the way I originally described. Not other ways I could do it. The answer I’m looking for is some sort of formula or rule that automatically blanks out the old cell with their name when selecting them in a new cell.

[#8 · Accepted solution]

@sophiemarie92 

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".

[#9]

@sophiemarie92 

I just don’t want the actual work flow of moving someone being in a table to the side like that. I prefer the cleaner look of just the “zones” being visible and hiding the table with the names to make it easier for them to glance at and print off.

I copied your layout images in what I did. Your original layout didn't do (or even hint at) what you're now saying you want.

It's easy to separate the input from the output, with only the latter visible to "them"

See the attached.

[#10]

Respectfully sir, I’ve been saying I wanted the exact same thing since the very first post. A method to automatically clear the old cell when I select them somewhere else. I don’t want the workflow of “moving someone” to happen in the table of names because I prefer to keep that table hidden. The only reason I included it in the example was to show you. My leads wouldn’t know how to hide/un-hide the table used to move people in the method you proposed.

[#11]

This is exactly what I was looking for thank you so much!