Referring to a sheet in an external workbook by position, not name

[Chief Wiggums]

I'm trying to summarize some data from an external workbook. The problem is that the worksheet names there are months (Jan2009, Feb2009 etc.) and the sheets rotate (change position) with time. In the summarizing workbook I would like to use a function to refer to (for example) cell A1 on the third worksheet, regardless of the current name of that worksheet. So I might have a cell in the summary workbook that looks something like
"=CoolFunction(ExternalWB.xls, 3, A1)"

The brilliant Chip Pearson has what might be helpful at http://cpearson.com/excel/sheetref.htm -- scroll down to "Getting The Name Of A Sheet By Position Number" . . . SheetNameOfIndex is a function that I can get to work *within the summary worksheet* (as in =SheetNameOfIndex(4) ) but I can't make it work to return the name of a sheet in an external workbook.

Anyone have any ideas, whether in using Chip's function or in a different direction?

Thanks kindly --

[rylo]

Hi

In your calling workbook try

Please Login or Register  to view this content.

As long as the other workbook is open, then called

=mycoolfunction("wbname.xls",1,"a1")

However, if the other workbook is closed down, then it will give an error. Didn't work through Chip's example. Does it work when the other workbook is not open?


rylo

[Leith Ross]

Hello Chief Wiggums,

Chip's code uses the Application.Caller function. This will only work when executed in the workbook the macro is in. You can refer to worksheet or chart in the main workbook or another by using Sheets collection and qualifying it with the workbook using the Workbooks collection.

Example

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Chief Wiggums]

Wonderful -- this works perfectly and I am very grateful.

Thanks to both of you. BTW, I challenge anyone to show me a forum (Excel or otherwise) where the first two responders to a question have a combined 9000 posts! Not many, I say.