How to create master database with separate worksheets?

Copper Contributor

So a weekly spreadsheet is created with a list of dates, names, times and notes. I have finally added all separate sheets (copied & paste) onto one workbook however seek to find an efficient way to add the information onto the master workbook without copying manually.


I also seek to gather all information from the separate tabs together onto a master sheet. I like to apply a filtered formula that also distinguishes duplicate names and remain aligned with new adjustments/information over time all onto the master sheet.


I currently use microsoft 365 - I am open to use formulas / vba as an option.


See images attached here of my current layouts - any help much appreciated.





1 Reply


I currently use microsoft 365 - I am open to use formulas / vba as an option.


It's good that you're open to various possible resolutions. May I ask if you're open to radically changing the way you collect data in the first place?


I ask because those images bear many indications of being a process that began in the days of keeping records on paper-ledger sheets, when it made sense to have a weekly sheet, collecting Sunday through Saturday (in your case Thursday through Wednesday) events on one sheet. Transferring that kind of paradigm to the computer, however, often fails to take advantage of what the computer (equipped with software such as Excel) can do by (at the input end of things) just taking raw day-by-day entries into a large single database and then processing it, summarizing it, reporting on it at the output end.


Here, for example, you are taking weekly data and wanting to put it together into a master sheet. By all means, it makes sense to have a master (all encompassing) sheet; what ideally could be done (IMHO) would be to go to the trouble to create that master sheet (AKA a database), and then continue to maintain it as a master database, running whatever weekly/monthly/quarterly reports are needed from that master.


Are you open to re-thinking things along those lines? It wouldn't be easy, I suspect, but I know if we were sitting down face-to-face (rather than exchanging messages on this forum), these would be questions I'd want to ask. I suspect others would too. It's also possible that there are some who have an easier way to accomplish exactly what you describe. So feel free to ignore my questions if you so desire.