How to automatically refresh MS Access tables in excel, without breaking formatting of other cells in worksheet

Question

I have an inventory spreadsheet that contains a table of records imported from a Microsoft Access database. To the right of this table there is a field for workers to input the amount currently in stock for each item. To the right of that is a "unit conversion calculator" that helps them correctly convert between different units of measurement.

Here is a screenshot of how it currently looks:

I am currently having several problems with this, however, and was wondering if anyone could help me fix them:

1) I have several of these sheets (one for each of our food ordering departments). I currently have to refresh them all individually, each time I open the spreadsheet. Is there a way to make all tables imported from MS Access automatically update upon load? I'm trying to make this as simple as possible for end users, and don't want them to have to manually refresh the DB data.

2) When I refresh the data from Access using the [Data] --> [Refresh all] button, if it adds new rows, it breaks the formatting of all the cells to the right (e.g. the unit conversion calculator). Here is a screenshot of what breaks after update:

How do I make it where it doesn't break the formatting of the unit conversion calculator (I don't want it to affect anything outside columns A-F)? And how do I make the green highlighting in the "Units in stock" column carry down to the new cells that are created?

Thanks!

Solution

First, to refresh the data when the file is opened, you need to go into the connection properties (Data tab > Connections group) to enable Refresh data when opening the file.

The best solution to not messing up your calculator is to move it to its own tab. Any time a table is updated it adds or removes rows to match the data in the table you are connected to. It also looks like there are some filters being applied. Those hide rows also. All of these factors will mess up the formatting of your calculator.