Referencing a Sheet Number in a Chart

[Jane West]

Does anyone know how to reference data by a Sheet Number rather than the
Sheet Name? We have a Workbook that is updated every week. The Sheet Name
changes every week (it is named by Week Ending date), but it is always
Sheet5 - the 5th tab postition, the 5th Worksheet.

In a Chart, I define the series data range as:

='WE_01.27.04'!$B$3:$B$9,'WE_01.27.04'!$F$3:$G$9

The worksheet the chart is on is in the same workbook as the data. The data
is on Sheet5, the chart is on Sheet8. I found these references and tried
variations, but couldn't get it to work:

='http://www.someones.homepage/[file.xls]Sheet1'!A1 or
='ftp.server.somewhere/file.xls'!MyNamedCell

I wanted to use something like:
='Sheet5'!$B$3:$B$9,'Sheet5'!$F$3:$G$9
but it doesn't work. Any ideas?

Thanks,
Jane

 

[Frank Kabel]

Hi Jane
AFAIK you'll need a UDF to get the sheetname for a specific
sheetnumber. E.g.
Public Function Get_SheetName(pos As Integer) As String
Get_SheetName = Worksheets(pos).Name
End Function

Now create a defined name for your charting series. e.g.
Chart_x: =INDIRECT("1" & get_sheetname(5) & "'!$B$3:$B$9")
Chart_y: =INDIRECT("1" & get_sheetname(5) & "'!$F$3:$F$9")

In the series dialogbox for your chart enter the following:
X_Values: ='your_workbook_name.xls'!Chart_x
Y_Values: ='your_workbook_name.xls'!Chart_y

HTH
Frank