The thing that gets me is you said:
"my company wants to ...[use] the master spreadsheet in Access"

And:
"the master sheet ... will not include ALL the aspects"

I think there's a problem, there. Sounds like the current plan will
lose some data when your final database is engineered.


Is this supposed to be one file, with many sheets? Or many files?


Not a good long-term solution, but in a short-term pinch:
If your client list is fairly static, not being expanded greatly, you
could enter the name or number (something unique and sortable) in the
master sheet and use VLOOKUPs for the other desired data. Esp if this
is all one file.

Or you could add a column of straight 1,2,3 numbers in a new Column A
of all the files (hide it if you don't want it visible) and do
VLOOKUPs on that, this way you can add a whole bunch of extra rows for
each file to cover new clients that will be entered. Maybe base the
numbers on the file name to make them more unique:
X234_001,X234_002,etc.

The Vlookups can be sorted (perhaps automatically by event code) in
the master sheet just like regular data as long as the first column of
these numbers gets sorted with it. And if you have some additional
programming to do later, you can always sort on Col A again.


The places where things can go wrong are the same as any other Excel
file. User error/stupidity. Can try protection to limit damage.