What's the best way to go about using a single excel database across multiple workbooks?

0
votes

I have a staff database which is regularly updated. there are separate workbooks which need to use this database, such as a workbook for absence management, and another for annual leave, supervision, etc.

My office currently edits a staff members details separately in each workbook. This leads to a lot of inconsistency.

Can I write some vba in the main staff database workbook to add any changes across all workbooks? Is this the only real way to go about accomplishing something like this?

In case this problem appears too vague. Think of Workbook 1 with a column for names, ages, telephone numbers. When this database is updated, I'm looking for the most efficient way to make these changes in near identical workbooks 2, and 3, which are located in different folders on the computer.

1
excelvba
Just use MS Access? – Doug Coats
Or SQL Server if you have for large databases – Shai Rado
@WillacyMe Trust me when I say this: Attempting to build a database in MS Excel is a path that will lead to madness. AT MINIMUM, your office should move to MS Access (and even then I'd say it's not going far enough). MS Access has extensive interoperability with MS Excel, so you'll find that you'd only be adding new abilities, not taking any abilities away. – Denny S. Temple
Unfortunately the company I work won't allow for SQL or any programming language like Python of R which would help a great deal. I'm unsure they have MS Access but if they do I'll certainly make use of it. Looks like rebuilding databases into one cohesive access file is what I'm left with, and then using excel for more analytical tasks? – WillacyMe
Yes, a RDBMS for data management and Excel for analysis not practical within db, although I expect can do a lot there. A timekeeping db can be a challenge because data entry forms often can't, or not without great difficulty, have the same design as paper forms such as Time and Attendance sheets where days are horizontal. – June7

1 Answers

2
votes

I will be teaching myself to use MS Access.