I am trying to consolidate data from several workbooks into a single summary spreadsheet. The source workbooks are created as part of a report generation/export process. The summary workbook is an Excel 2003 file I have created myself.
I am using cell references (i.e. =C:\File Location\[File Name](worksheet 1)'$A$1) to extract the desired data from the source files. This is working fine except for in one case. One of the workbooks has variable worksheet names based on the date the report is run. Therefore, the formula breaks everyday because the worksheet name reference changes. Is there any way to create a variable reference to this worksheet so that whatever the date is on the worksheet tab, the formula still locates the cell reference I need? The workbook (file) name, and cell references will always be constaint.
I thought INDIRECT would do this, but all the forum entries I find seem to indicate that it only works for variable cell names, not variable worksheet names.
Bookmarks