I've been able to get the sheet name to populate into a cell on the worksheet
using the following function:

=RIGHT(CELL("Filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))

Using this value in place of the "Wk1" in the link would solve my problem;
however, I don't know how to insert this into the equation:
='C:\My Documents\My Books\[Book2.xls]Wk1'!L8

CVinje



"CVinje" wrote:

I have two workbooks (Book1 & Book2 for naming purposes), each have data
entered into them on a weekly basis for the year. Book1 links to cells in
Book2. Each workbook has been setup to automatically create a new sheet,
incrementing the label from starting at Wk1 and going from there by execution
of a macro & command button. The links in Book1 also need to increase
incrementally instead of just being copied over and still linking to Wk1- it
should change to the same cell, etc; just change the sheet referenced to
increment by 1 (to Wk2, etc). Example of the link (using IF function to show
empty cell if there is no value in linked cell):

=IF('C:\My Documents\My Books\[Book2.xls]Wk1'!L8="","",'C:\My Documents\My
Books\Book2.xls]Wk1'!L8)

Just in case it would be helpful, here is the code to create the new sheet
and rename it incrementally:
Dim i As Integer, w As Worksheet
Application.ScreenUpdating = False
ActiveSheet.Copy Befo=Sheets(1)
Set w = ActiveSheet 'the copy
On Error Resume Next
i = 1
Do
Worksheets("Wk" & i).Activate
If Err.Number < 0 Then 'sheet name doesn't exist yet
w.Name = "Wk" & i
Exit Do
End If
i = i + 1
Loop
On Error GoTo 0
w.Activate
Application.ScreenUpdating = True

Any help would be greatly appreciated.

CVinje