Referencing the first sheet, no matter its name.

**madadd** · 04-01-2011, 12:32 PM

Hi,

Is there a way that you can refer to the first sheet in a workbook, without knowing its name?

**protonLeah** · 04-01-2011, 02:18 PM

Sheet1.Range( ...
Sheets(1). ...

**madadd** · 04-01-2011, 02:30 PM

Hrm. It turned my #REF error into a #value error. Just for reference, I'm working with this equation:

=INDIRECT.EXT("'N:\QB SKU Project Backup\NON-PROTECTED\["&$B$43&"]COST SHEET'!$"&CHAR(63+COLUMN(B23))&MOD(ROW(B23),10)+7&"")

Which turns into this given your recommendation:

=INDIRECT.EXT("'N:\QB SKU Project Backup\NON-PROTECTED\["&$B$43&"]"&sheets(1)&"'!$"&CHAR(63+COLUMN(B23))&MOD(ROW(B23),10)+7&"")

I've tried this with different files which give me the correct result using the first equation, but will turn into #value error when inputting the "&sheets(1)&"

**6StringJazzer** · 04-01-2011, 08:46 PM

The code from protonLeah is how you would reference the first sheet in a VBA macro, not an Excel formula.

In formulas, you can refer to a sheet by name, but not by its position. You would need to write a UDF like this:

Public Function Sheet(i As Long, s As String) As Range

   Set Sheet = Worksheets(i).Range(s)

End Function

This will take a sheet index and a string giving a range reference, and return the reference.

This will return the contents of cell A1 on the first sheet, whatever that is:

=Sheet(1, "A1")

The function call can be used anywhere a range can be used:

=VLOOKUP(A1,Sheet(1,"A1:B5"),2,FALSE)

**6StringJazzer** · 04-01-2011, 08:53 PM

Because you are building a string and using INDIRECT, you could also use a function like this that returns a sheet name:

Public Function SheetName(i As Long) As String
   Application.Volatile ' force updates
   SheetName = Worksheets(i).Name
End Function

BTW because these functions do not refer to a particular range, you would also have to force them to be updated any time there's a change. However, changing the order of worksheets would not force the update. This gets a little tricky and I haven't taken the time to think through the details, but this gives you the idea of what would be involved in something like that.

**madadd** · 04-04-2011, 11:02 AM

Hmm. So I couldn't get the Sheet() function to work, but that was ok, because the sheetname() proved to be a better fit. However, while I can get it to work in a standalone scenario, giving me the sheet names of the current workbook, adding it to my indirect function I believe it's trying to pull the current workbook sheet names in lieu of the referenced workbook sheet names.(ewww, a run-on sentence). I don't know UDF or VBA coding at all, but I believe there needs to be a file reference parameter within the sheetname() function. Although if at all possible I'd like to avoid that, as having the filepath and name twice in the same equation seems lengthy and ridiculous.

Please help!