worksheet names in formulas

[obrien2010]

I need to crerate a formula (or a macro) that will reference a worksheet by sequence number instead of name. I need to apply this macro to multiple workbooks and the tab names may not always be equal.

=MIN('Module9 - 6748'!J2:J100,'Module4 - 6748'!J2:J100) (named used in formulas)
would like it to be:

=MIN('FIRST SHEET'!J2:J100,'SECOND SHEET'!J2:J100

Somehow referencing that this is the first tab, second tab, etc... witout having to rename all tabs on hundreds of sheets...
Any thoughts?

[JBeaucaire]

My understanding is that if you rename a sheet, ALL formulas that refer to it are changed automatically, too. You shouldn't have to edit any formulas.

[6StringJazzer]

My understanding is that you want to be able to copy a formula from one workbook to another. This formula references ranges on other worksheets, and the names of worksheets vary from workbook to workbook. You want your formula to be independent of what the worksheets are named, and use only their positions on the list of tabs.

The following UDF will do what you want. You need to create a new Module and put it in every workbook that needs to use it.

Please Login or Register  to view this content.

SheetIndex is the index of the worksheet. The index is based on the tab order.

RangeRef is a string that provides the range.

In your example, you would use the following formula:

=MIN(SheetRange(1,"J2:J100",SheetRange(2,"J2:J100"))

[obrien2010]

That worked like a charm. Thanks!!!