Forum Discussion
Drop down list "moving" selection when selected in another cell
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.
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.
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?
