Linked Cells, Increment Sheet Number Ref.

[CVinje]

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 Before:=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

 

[CVinje]

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

 

[Jacob Skaria]

Use INDIRECT()

Please note the apostrophe just after the first double quote....The below
formula would refer book2.xls>current sheet>cell F8..

=INDIRECT("'[Book2.xls]" &
MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)& "'!L8")

OR

=INDIRECT("'" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)&
"'!L8")

If this post helps click Yes
---------------
Jacob Skaria

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

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 Before:=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

 

[CVinje]

Thank you for the reply, your proposed solution works; however, not if the
sheet is sorted - as both my sheets frequently are. It seems linking the
cells may be the only method to properly associate the information. Any help
with including a cell reference into the link where the entry "Wk1" is would
probably solve my issue; but I'm still stumped on that.

Thanks again for your time,

CVinje

Use INDIRECT()

Please note the apostrophe just after the first double quote....The below
formula would refer book2.xls>current sheet>cell F8..

=INDIRECT("'[Book2.xls]" &
MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)& "'!L8")

OR

=INDIRECT("'" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)&
"'!L8")

If this post helps click Yes
---------------
Jacob Skaria

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

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 Before:=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