Linked Cells, Increment Sheet Number Ref.
 

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

Linked Cells, Increment Sheet Number Ref.
 

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

Linked Cells, Increment Sheet Number Ref.
 

Use INDIRECT()

Please note the apostrophe just after the first double quote....The below
formula would refer book2.xlscurrent sheetcell 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


"CVinje" wrote:

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

Linked Cells, Increment Sheet Number Ref.
 

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

"Jacob Skaria" wrote:

Use INDIRECT()

Please note the apostrophe just after the first double quote....The below
formula would refer book2.xlscurrent sheetcell 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


"CVinje" wrote:

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