Distributed Workforce Excel Based Online Tool

The main problem with Excel for such a purpose is that for everyone to be able to update the workbook simultaneously, it must be a shared workbook. To limit people to only seeing their data, most likely requires VBA code. You can't use VBA code in a shared workbook.

Also any scheme using Excel like this still has other people's data visible if a person were to take the time to hack around. If this isn't an issue and it's only a matter of convenience to filter the data, then this isn't an issue.

When you have to share data and limit data at the same time, a database such as MS-Access is better. Put the main database (back end) in the shared repository. You can distribute different front ends to different users depending on their needs. Security is a little better in that the only people who can see the data are the people who have permissions to the shared drive.

I suggest using the database to manage the data and Excel to report it. It takes advantage of the strengths of both programs. Also, there's a political side: senior managers usually don't know squat diddly about databases, but they do know Excel.

We make use of back end and front end databases in our organization. The only people who need MS-Access licenses are the developers. The rest either get the data through Excel or, if they have to do data entry, a runtime-only license in Access. Runtime only licenses are free, and they keep people from changing the structure of the database. The only thing they can do is access the forms you provide for data entry and run reports you set up for them. Keeps them out of trouble.

You say that you are poor with Access. If it is that complicated that basic Access won't hack it, then it will be absolutely nightmarish to manage in Excel. I'm assuming that what you have is basically a "flat file," that is, a single sheet with column headers and rows of data. If this is the case, then only a basic understanding of Access would suffice.

On the other hand, if the spreadsheet is laid out in some sort of a report format where one set of rows and columns means one thing and another set of rows and columns means something else, then Access can still be used, but it may be more complicated.

Licensing should not be an issue. There are two ways to access the data. If you want to report the data, it can be pulled into Excel easily, regardless of the version of Access you are using. I've even pulled Access data from an Access 97 database with Excel 2013.

The other way to access the data is from the front end. The front end is where people can bring up data and change it or add data. Using continuous forms, you can make the data look very much like a spreadsheet. You will not have to buy a license for front end users. You can download for free, run-time only MS-Access licenses from Microsoft. The only person who needs a license is the developer.

Having said all this, I have built applications to make Excel look and act like a database. It takes some coding, but basically I read / write data from a CSV file. The issue with this is concurrency (not an issue for me as these are one user at a time programs in my environment). If I open the file, then you open it and make changes, and then I close my file with my changes, I clobber your changes.

It would take a bit of coding to compare the spreadsheet with the original CSV file to determine what was added, deleted or changed and to "lock" the primary key. These are things databases do automatically.