Home > Forum Home > Excel Business Valuation Template > Employee Office Mailbox Assignment Master and A-Z List Share

Employee Office Mailbox Assignment Master and A-Z List

Excel Help for Employee Office Mailbox Assignment Master And A-z List in Excel Business Valuation Template


Forum TopicPost Reply Login

Employee Office Mailbox Assignment Master And A-z List

Rate this:
(3.5/5 from 2 votes)
Confused I am taking over the company's employee master list and mailbox assignment list spreadsheets and I'm thinking that there has to be an easier way to do this.  Currently I have two separate spreadsheets with the exact same information.  One spreadsheet has all of the mailboxes in order and the other spreadsheet has all of the employees listed in alphabetical order, making the mailboxes all scrambled.  Which is fine, I don't care that the mailboxes are scrambled.  It's alphabetized so that the employee can easily find their assigned mailbox number by searching for their last name.

The issue I am having is that I have two different groups of employees, Armed & Unarmed.  In the Master List, they're all one the same sheet mixed together.  On the A-Z sheet, one group (Armed) is separated from the larger group (Unarmed).  So column A has the mailbox number and column B has the employee name and those two columns are for the Armed group.  Columns C & D, E & F, and G & H are for the larger (Unarmed) group with the first columns C, E & G showing the mailbox number and columns D, F & H showing the employees names in alphabetical order.  

Previous people would update the Master List and as the employees get hired or leave the company.  So there are empty spaces scattered throughout the entire Master List.  When we hire a new employee, they should get placed in the first empty space available.  When someone leaves the company, you simply delete the name but not the mailbox number because that's the order in which the Master List is organized.

I want to be able to update the Master List and have the mailbox number and name show automatically on the A-Z list, preferrably in Alphabetical order if possible. But if I have to sort the list manually, then I am fine with that so long as it keeps the two groups separate.  I don't know how to do that since there isn't anything indicating who belongs in which group.  I just finished cleaning up the A-Z list finding multiple people listed twice, once in each group for some reason.  They were easy to find because they are members of management and they don't need a mailbox.

I'm new to this forum and I don't see how to attach my spreadsheet so that people can see what it is I am dealing with so I have to try and insert a snippet as that's all I can see to do for anyone willing and able to assist me.

Excel Spreadsheet:
 A B C D E F G H
ARMED OFFICERS UNARMED  OFFICERS    UNARMED OFFICERS  UNARMED   OFFICERS
Box   Name Box    Name   Box   Name  Box Name 
I14  Akin, W  E7  Allen, D  J3  Foster, T  I12  Pollard, D
G5  Boyd, J  H2  Anderson, D  B7  Foxworth, E  N2  Quickert, D
F5  Brown, T  J1  Bah, A  K13  Frazier, S  N15  Rafiyq, A
B12  Campbell, R  A6  Baldwin, S  F3 Fuller, R   I13  Ressler, H
J6  Casey, C  L7  Bloomfield, B  J13  Gardner, W  C2  Reyes, I

The example above is of the A-Z list that is completed manually from the Master list.  If what I am wanting isn't possible, then I guess I'll just continue to do it the way that it was designed.  But I believe there has to be a better way to do this so that I am not cleaning it up every time someone else who doesn't know what they're doing touches it.

Please help, even if it's just helping my already overworked, exhausted brain come up with an idea that it should already know but is too tired to think of it.  I am usually pretty good with Excel and formulas.  But with the office manager quitting, me discovering everything she half-assed and I'm busy cleaning it all up and my brain is scattered in many different places.
 Posted by on
 
Replies - Displaying 1 to 2 of 2Order Replies By: Most recent | Chronological | Highest Rated
Confused
Rate this:
(4/5 from 2 votes)
thank you so much  space bar clicker
 Posted by on
Confused
Rate this:
(4/5 from 2 votes)
I think that your best option here is a combination of INDEX and MATCH.

This gives you flexibility on horizontal and vertical matching and is more efficient than LOOKUP formulas. See:  https://exceljet.net/index-and-match
 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Employee Office Mailbox Assignment Master and A-Z List in the