My company currently runs on Excel. Practically everything is run in some Excel file or another. We have several very large reports that need to be updated at specific times throughout the year. These reports basically have 1 summary page that pulls data into it via VLOOKUP's and location settings picked from a dropdown List. On top of that, rather than have client specific files reference this large report, each file has to be updated separately with the exact same data. It's a colossal time sink, and while I think Excel does wonderful things, having EVERYTHING on it is wasting time and opening potential for errors. Each client currently has it's own file because we send these to clients and whoever made them was trying to keep the file sizes smaller.
I don't know a lot about Access so I just want to see if it's feasible to create a database where we have what the summary should look like and simply upload whatever source files there may be for each year and add a new option to select each time new data is added. Eg. SummaryPage would have an option to select year that could pull information in a similar fashion to a VLOOKUP from Data2014 or Data2015, and when 2016 rolls around, I could upload Data2016 and add that as an option. Then I could setup one summary page for each client's information that pulls from the same data sources based on year selection.
There would be a bit more to it, but I think if the above is possible then it should be able to handle any other criteria needed such as multiple criteria selection (if year is 2015, then have options to pick quarters from 2015), multiple data sources, etc. Is this possible to create with Access? Are there any things I would need to look out for or simply wouldn't be possible? Or is there an easier solution in continuing to use Excel?
Bookmarks