Monthly reports - Using variable in cell hyperlinks

Copper Contributor

Looking for some advice on how to streamline my reporting system and specifically if a variable file name can be used in cell hyperlinks to update values for my templates.

 

Picture always tells a thousand words so here is the current system I have created.

 

 

Report flow chart.png

 

Our monthly data is manually imported into a workbook across 5 spreadsheets (limitations of reporting software that we are obtaining our data from) and this is collated onto a single spreadsheet and exported via VBA code with file name of current month & year. A copy of which is sent to a backup folder as well as being imported into my access database for 6 month & yearly reports.

 

The monthly reporting is currently done with excel spreadsheet templates that are then linked into the final word templates. Clunky but it works. Problem is that any changes require major surgery and while I am able to notice errors when they occur, if someone else needed to run the reports, I fear the worst.


So I would like to streamline it a little by splitting the reporting into two steps and by using forms, keep prying hands away from the back end. 

 

Is is possible to use a variable from say a form combo box to select this months collated data file where the cell links get updated? Or even better, to automatically select the most recent file from a specific folder?

0 Replies