Is it possible to reference a variable worksheet name in a formula?

[HelpExcel]

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.

[hoffey]

Hello, welcome the forums.

You might want to use a defined range that utilizes concatenate, that way you can change the workbook name dynamically. This code is not tested, but define a name with something like this:

=CONCATENATE("C:\File Location\Test.xls!'" & E32 & "'$A$1'")

Where E32 could = TODAY() ... Or however you have your naming structure for the worksheets, you may need to do a custom format.

See this separate thread for more insight:
http://www.excelforum.com/excel-misc...reference.html

[DonkeyOte]

INDIRECT will only work if target is an open workbook (it can be as dynamic as necessary regards file names, sheet names & cells).
If in your case (as implied from the links) your target files are closed then INDIRECT per se is not viable and you may want to investigate Laurent Longre's morefunc.xll add-in & specifically the INDIRECT.EXT function.