Auto-Populating Staff Member List

Occasional Visitor



So I am trying to 1. impress my boss, and 2. create an easy to use and intuitive staff list that only needs updated on one sheet (the first sheet in the book) and the other 3 would automatically populate the same info correctly. Basically my final goal would be to learn how to get the information on sheet 1 (staff phone list) to populate in the correct order and alphabetically by last name, without getting a #SPILL error on the other 3 sheets in the book (Absences, Counselings, Staff Signatures) via formulas... or however it has to happen. I have obviously taken out all personal information but if someone could take the sample file I have provided and let me know how this is possible, I would gravely appreciate it! 

Thank you!!



1 Reply


Firstly, I don't really understand why you would want the last three sheets to be automatically updated and sorted, based in the information in the phone list. You mention the #SPILL error, meaning that you are working with dynamic arrays. Correct? 

If you add a person to the phone list, the sorting of the staff member names will be dynamically updated and sorted, but the data that you have entered for each of them will not follow. Is that really what you want ot happen?

Ideally, you would collect all the information you want to track in one structured table (Employee ID, Date, Event type) And then you would also maintain a table with employee ID's, Last name, First name, Phone number and Signature. If you are not on a Mac, you can then create summaries from these databases (per employee, per event type or whatever) using the Data Model and Power Pivot.


By the way, from a cosmetic point of view, I would get rid of the the narrow columns and rows for the borders (it looks ugly, anyway). And these empty rows and columns insert empty spaces in what otherwise could be a structured table.

Then, also avoid using merged/centered cells. They only cause trouble. I've made a few changes to several columns the first two sheets (shaded green cells at the top). Up to you to do the rest, if you like it. But, before spending a lot of time on the cosmetics, think through what you really want to achieve.


Edit: Forgot to attach the file. Added now!