I have a base sheet that needs to be able to be saved each month and
point to a separate file that will be saved each month. My final users
of this base sheet will not be able to get in and change the reference
to show a call from file datajan06.xls to datafeb06.xls, etc.... so
Data and .xls will always be the same, however the month and year will
need to change.

I would like to make a cell that says Jan06 and the user can change
that to Feb06 which will then tell the reference to change the file it
is pulling from ... ie. changes dataXXXXX.xls from datajan06.xls to
datafeb06.xls and thus changes the file it is pulling the data from...

I have used indirect to create a field reference to a field with a
formula of ="data"&Attendence!C1&".xls" so that if C1 on the attendence
page says Jan06 then the file is datajan06.xls.

This does not seem to translate into a working formula...

=IF(Attendence!B5="", "", '[dataXXXXX.xls]Sheet1'!$H3)

Any suggestions?

Ed,

INDIRECT will work, but you need the file referenced to be open to actually have the data extracted.

Otherwise, you could use the worksheet's change event.

The example code below assumes that you will type Feb06 into cell A1 to change the formulas of the
current sheet. Copy the code, right-click on the sheet tab, select "View Code" and paste the code
into the window that appears. You will also need a cell named OldFileName, and both that cell and
your cell for filedate entry need to be formatted for text. Also, the cell OldFileName should have
Jan06 (or whatever the current cell link is) entered into it prior to starting.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myOldFileName As String
Dim myNewFileName As String

If Target.Address < "$A$1" Then Exit Sub

Application.EnableEvents = False

myOldFileName = Range("OldFileName").Text
myNewFileName = Target.Text


Cells.Replace What:=myOldFileName, _
Replacement:=myNewFileName, _
LookAt:=xlPart

Range("OldFileName").Value = "'" & myNewFileName
Application.EnableEvents = True

End Sub



wrote in message ups.com...
I have a base sheet that needs to be able to be saved each month and
point to a separate file that will be saved each month. My final users
of this base sheet will not be able to get in and change the reference
to show a call from file datajan06.xls to datafeb06.xls, etc.... so
Data and .xls will always be the same, however the month and year will
need to change.

I would like to make a cell that says Jan06 and the user can change
that to Feb06 which will then tell the reference to change the file it
is pulling from ... ie. changes dataXXXXX.xls from datajan06.xls to
datafeb06.xls and thus changes the file it is pulling the data from...

I have used indirect to create a field reference to a field with a
formula of ="data"&Attendence!C1&".xls" so that if C1 on the attendence
page says Jan06 then the file is datajan06.xls.

This does not seem to translate into a working formula...

=IF(Attendence!B5="", "", '[dataXXXXX.xls]Sheet1'!$H3)

Any suggestions?

=IF(Attendence!B5="","",INDIRECT("'[data"&C1&".xls]Sheet1'!$H3"))

If C1 = "jan06"...
BUT, datajan06.xls has to be open!

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



" wrote:

I have a base sheet that needs to be able to be saved each month and
point to a separate file that will be saved each month. My final users
of this base sheet will not be able to get in and change the reference
to show a call from file datajan06.xls to datafeb06.xls, etc.... so
Data and .xls will always be the same, however the month and year will
need to change.

I would like to make a cell that says Jan06 and the user can change
that to Feb06 which will then tell the reference to change the file it
is pulling from ... ie. changes dataXXXXX.xls from datajan06.xls to
datafeb06.xls and thus changes the file it is pulling the data from...

I have used indirect to create a field reference to a field with a
formula of ="data"&Attendence!C1&".xls" so that if C1 on the attendence
page says Jan06 then the file is datajan06.xls.

This does not seem to translate into a working formula...

=IF(Attendence!B5="", "", '[dataXXXXX.xls]Sheet1'!$H3)

Any suggestions?