Dear Excel People,

I am trying to find a best way to create a template for data, that ideally will be dynamical, and can't get my head around what would be the best approach.

Idea is presented in the following example:

Have a 'master table', where first column is dynamical and is driven by formulas. (Idea behind this is to have master entries, so that only one thing should be updated)

User A
User B
User C
User D
User E

So now you have you list of users, and this list is dynamically driven from elsewhere.

Now you want each user to fill in some data manually. For example, expenses in each month.

Expenses Month 1 Expenses Month 2 Expenses Month 3
User A <manual hard-coded number> <manual hard-coded number> <manual hard-coded number>
User B <manual hard-coded number> <manual hard-coded number> <manual hard-coded number>
User C <manual hard-coded number> <manual hard-coded number> <manual hard-coded number>
User D <manual hard-coded number> <manual hard-coded number> <manual hard-coded number>
User E <manual hard-coded number> <manual hard-coded number> <manual hard-coded number>


What I see as an issue is: if in the 'master' table list of users will be updated, i.e. users removed and added, this will screw up your first dynamical column in the dataset above, changing first column in the table above and hence screwing up data, as manually entered values by user will be aligned to wrong user names, since hard-coded data has not been changed and values have been.

Although quite easy problem, I can't get my head around it.

If there are any best-practice methods or any creative suggestions, I would very much appreciate you sharing those.

Many Thanks,
Michael