Start by not using Excel.
Excel is a spreadsheet, and what your are describing is a database - and while you can treat excel as a database, it really isn't very good at it, which means that whatever you try to do with it gets very flaky, very quickly.
It's kind of like when you have a hammer, everything looks like a nail. But when what you are actually hammering are screws what you do is mess things up even though it appears to work at first glance.
And that's before you get to the big problems engendered by just one of your requirements:
Quote:
-Work online
Online means a client server system, with many clients talking to a single server.
And servers do not have Office installed, because that costs money and the users can't see it anyway!
And worse ... Excel uses a file and expects a single user to be accessing and modifying it at a time: in a multiuser system there could be hundreds of people reading, writing, and updating information simultaneously. Excel isn't designed for that!
Use a database, and separate the tables in your sheets into actual tables, with defined relationships between them that the DB engine can both check and enforce - your whole task (once you get over the learning curve) will be easy to write, easier to understand, easier to modify, and much, much more reliable.
Check what your hosting service provides - usually SQL Server or MySQL - and use that. They are both designed to work with multiple users, as well as read, write, and update row based table data.
By all means export reports to excel if you really need them - but don't use a spreadsheet as a database!
Submit an article or tip