I'm looking to create a master database (in Excel, preferably) that will pull all its information (records) from multiple workbooks that will be saved in the same network drive, but different folder path. These multiple workbooks will be identical, same number of worksheets, same layout, same format, etc., they will just contain different information. The master database file will have two main uses:
Managers will open file directly and generate reports based on desired criterias (in Excel I'm planning on achieving this by using filters)
2. Daily Automated Reports
At a specified time on weekdays, the file will send e-mails that will contain specific reports, to multiple managers
Although I'm a novice at vba, I believe that all these tasks can be achieved through vba code and/or excel formulas.
In the office we all have MS Access 2010 as well, but I'm hesitant to use Access because the end-users (managers) to this master database do not know how to use this program.
I'm hoping to get help with the following:
1. Question 1
Does my logic sound right?
2. Question 2
Which function of excel would work best to pull data from closed workbooks on a daily basis? (I read somewhere that the INDIRECT function works well, but not sure if it'll be a slow process)
3. Question 3
Since we all have MS Access, would this program be a better option to pull the data from the multiple workbooks and somehow link Excel to Access (for purposes of the front-end to the users)
Any help that you can provide me will be greatly appreciated.