I am working on a report workbook that consolidates information from other workbooks for easy viewing and interpretation. It is meant to gather product yield information entered by team leaders in one workbook, for the plant leader to review in another.
I'm thinking I need to create code/formula that references a number entered in another workbook, however, this situation is a bit more complicated by the fact that the "source" workbook I need to pull information from will no longer be accurate after a week. See, every week a new "source" workbook is created and indexed under the filesystem by Period folders (1-13), and inside those folders each week has its own workbook (i.e. WEEK 1.xlsx, WEEK 2.xlsx, etc). So each week I would in essence be dealing with a completely different workbook.
A few workarounds came to mind - the first would be the simplest. I could have our admin continually update one file only, and save a copy each week for recordkeeping purposes. This seems to be the easiest to implement, although asking people around here to change their ways is like pulling teeth sometimes!
Which brings me to my question - Is there some way to create a reference to a cell in another workbook, but in the formula that points to the source workbook, insert a variable in the filepath that would change based on user input?
=SUM('C:\Reports\Period x\[WEEK x.xlsx]'!C10:C25)
Where lowercase "x" would be a value defined by the user through a drop-down box allowing them to select both the Period and Week they wish to view. For instance, if they selected Period 3 Week 4 the formula would reference:
=SUM('C:\Reports\Period 3\[WEEK 4.xlsx]'!C10:C25)
I'm sure this is something to be accomplished in VBA, however, I am not very proficient with that. Can anyone provide some insight to this issue? Thanks!!