ignore worksheet name?

[Derek Jacobson]

Sorry to keep on bothering the list but I've never had
such a complicated task.

I have one workbook that references another. The source
workbook is created by another program and assigns a
seemingly random name to the only worksheet in the
workbook.

I need to reference that worksheet but do not want to
manually change the links to adhere to the new worksheet
name everytime. Ideally, I would like to type:

=[file.xls]A1

when I should type:

=[file.xls]RandomSheetName!A1

Because the source workbook only has one worksheet, is
there a way to tell excel to use the only existing
worksheet, regardless of name.

Please help.

Derek

 

[Steve Smallman]

Programmatically, this is no great drama, you merely refer to the first
sheet in the sheets collection (sheets(1)).

Using worksheet functions, it is not so simple, my suggestion is that you
update the sheet name in a single cell, and then use that cell to build the
text that comprises the reference.

e.g
in cell A1 (or wherever) you could manually enter the sheet name as text,
alternatively, on workbook_open you could open the target workbook, read the
name of the sheet(sheets(1).name) and then write that value to cell A1 (or
wherever)

then in cell a2 build a formula that uses that reference e.g. =INDIRECT("'"
& filename &"'" &A1&"!A2")

Steve