Programming approach: managing external data with an Excel application

Hi, looking for some general guidance on an approach to programming/designing a solution.

Background:
I have been working my way through improving the user interface and other features of an Excel application I've built and is currently used by my colleagues. The application helps our coaches to plan and design training for our clients (competitive cyclists, from professional level to local club amateurs) with the end result being a professional output in both presentation and content, and that is consistent with our coaching business values. We're only small, hence doing this stuff myself.

The system imports detailed race schedule data from our online client management/communication system, and key performance data from other specialist analysis software and has a range of tools for planning at an annual/season level right down to defining specific workouts to be performed on any given day over the next 1-4 weeks. Naturally there's quite a bit of information about our client as well. The science behind the training methodologies used are built into the logic of the application as well as performance management charts.

Output produced is a PDF which is uploaded to our client communication system, although it could also in future upload data directly to an online training system.

I won't go into the details of all that it does, but it's reasonably complex, for me anyway
I know major sports institutes that don't have anything as well thought through/effective/practical despite spending millions of bucks.

The issue:
Anyway, the issue is this: At present the system is designed such that a coach opens a new version of the Excel application for each client they service. It's a little cumbersome and I am looking for a better way forward for the tool.

My first steps to build on what I've done so far is to begin to move many user interaction features from worksheets to userform(s). I don't think all such functions can be moved to userforms (but never say never). e.g. one page has ~150 controls, plus automated display of much of the information coach requires to make sound training planning decisions.

But I think a more master-application with slave-data sets would be a better approach, and also enable other business solutions to be designed, such as workflow management tools for coaches (e.g. help them track which client has training due to be prepared/delivered, etc).

What I'm looking for guidance on:
What I'm interested in, is methods for how a "master" Excel application can work reliably with "slave" data workbooks (or other means of saving data), which would contain two primary sets of data:

1. slave client data, which would load up when user chooses that client to work on, and the data is securely maintained in a separate workbook or data file. At present there are several sheets of data saved for each client
2. slave system user data, mainly basic system set up controls, so that when upgraded versions of the system are provided, the user's system setting are maintained

At the moment I have macros that save current client and system data in the application to another workbook, so that it can be re-imported (using another macro) when I provide an application upgrade.

Ideally the process for managing the data should be fairly seamless to the user, all they need do is select from the main userform dropdown the client they want to work on, or to set up a new client and the relevant data collected and new slave dataset is created.
e.g. is it better for the system write directly to slave data sets, or just periodically save a copy of the data and import that data at the time it's needed?

Any thoughts on general approaches when you want a master-application - slave-dataset type of set up?

It sounds like it would be better if you used a database to hold your data. That way there's a single place which holds data in a uniform manner that's easily backed up, access is probably the easiest place to start, but something like sql server express might be more suitable since you can create procedures in the database and hold them centrally rather than in each client.

Fundamentally, Excel is not designed for a client/server architecture so trying to make it do so, whilst possible always feels like a bit of an uphill struggle. Databases however are designed to be used for this type of architecture so things are easier.

Just my two pennyworth. Though being a proud Englishman, I'm not really sure I should be helping any aussies get better at cycling - there has to be something we can beat you at

Thanks. Yes that makes sense.

I don't have much experience with databases (and I've no idea how one integrates with a db), so not really sure which way to turn.

Another issue is that coaches are located around the world, it's not like we all have access to an office/server environment, people are working from home, use their own PC, usually with basic Office software, so no idea how a db set up would work for a distributed working arrangement.

The system I provide them is compiled to a .exe file, such that they need Excel on their machine to run it, but they have no access to the underlying workbook elements, formulas, hidden sheets, tables etc, VB code is not viewable/accessible.

Originally Posted by Kyle123

Just my two pennyworth. Though being a proud Englishman, I'm not really sure I should be helping any aussies get better at cycling - there has to be something we can beat you at

BTW, while I'm an Aussie coach, most of our coaches are in the UK. One of my clients, an Englishman, set a new world record last year in Manchester for the 50-54 masters age category hour record - a tick under 48km for the hour.

Besides, comes the Ashes and the Poms will no doubt be whipping some Aussie cricket butt.

Our clients are located globally.