Create a master list that other sheets reference and auto update when master changes

I'd like to create a master list of employees in our company. I want the other sheets in my workbook to exactly mirror this list of employees, automatically removing or adding employees as I remove/add employees in the master sheet.

I've found lots of solutions that go the other way (create a master list from multiple sheets), but I want my master to be the source and the other sheets to be the destination.

The other complication is that my other sheets use tables to list the employees, so I'm unable to use a multi-cell array formula to reference the master list (I tried using a dynamic named range but that didn't work).

I've tried Index and Index Match but nothing seems to be working. Any suggestions?

1 Answer

The cleanest solution would be to use Access to store the employee list, and have Excel fetch data automatically in the database. This way, you can edit the information in Access directly and the changes will be visible in the various Excel files you're using.

It's fairly easy to do:

1. Create a new Access Database (in the file explorer, right click > New Microsoft Access Database)
2. Open the database
3. In the ribbon: External Data > New Data Source > From File > Excel
4. Select the Excel file in which your data is located (provided it's formatted as a table with headers)
5. Select "Import the source data into a new table in the current database"
6. Click next, then tick "First Row contains column headings"
7. Click next, and configure the fields to match your needs (Access should be able to detect the format)

Once your table is created, you can open it by double clicking on the table name on the left.

Now, in one of your Excel files where you need the employee list:

1. Data > Get Data > From Database > From Access Database
2. Select the .accdb file
3. Select the table you want to load
4. Click on "Load To" (small arrow) and select "Table"

You will now have a data table within your workbook that is automatically linked to your access database. Any change in the database will be seen in the Excel file automatically. You can also refresh the data by clicking the "Data > Refresh All" button.

Check Microsoft's help website to know more about Access