User defined variables in a path reference

Hello,

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?
For example:

=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!!

Re: User defined variables in a path reference

Quote from Richie(UK);624641

Hi Matt,

You could take a look at using the 'Indirect' worksheet function. It does, however, have a fairly significant drawback - it won't work as a link to closed workbooks.

There have been various 'fixes' attempted by VBA coders over the years. For example, the 'Indirect.ext' function found within the Morefunc add-in and also a UDF called 'Pull' (I'm afraid I can't remember who wrote that. Anyone else remember?). However, I believe that these fixes have 'issues' with the most current version of Excel - ie they no longer work!

You could, perhaps, try combing Indirect with the use of a VBA routine that would open all of the associated workbooks to update the data and then close them again. It's not very elegant but it may work if you don't have too many workbooks to work with.

EDIT: Found a link that to a YouTube video that discusses the Pull function (it was written by Harlan Grove)
http://www.youtube.com/watch?v=crbGfqEorXQ

Display More

Thanks for the insight Ritchie! I will check out that video for a possible solution.

Another workaround I thought of would be to place a macro in the template file that would be set to run at a certain interval, i.e. when the workbook is closed or printed. The macro would print the text of selected cells to a text file. A total of around 30 cells to output per week, I'm thinking this should be a pretty manageable txt file.

I found some code on another site but I'm still tweaking it to output the file to my liking. For example, the Chr(13) is outputting as "E-02" instead of a carriage return. But it looks promising based on my needs. Here is the code:

Sub MakeTXT()    
      Open "i:\etch_text.txt" For Output As #1
           Print #1, Cells(39, 8) & Chr(13) & Cells(66, 8) & Chr(13) & Cells(138, 8)
      Close
End Sub