Linked Cells - Have part of the link be variable or a formula?

[CVinje]

I have two workbooks, Book1 & Book2. Book1 has links to data on Book2; however, new sheets are created in each workbook, progressing from Wk1 to Wk52 for a year. When the new sheet is created, I have a macro that copies the Wk1 sheet, creates a new sheet named Wk2 and clears the data entered. This happens on both workbooks. The problem is that when the new sheet is created, the cell links still refer to sheet Wk1 in Book2, when I need them to progress just like the sheets do (i.e. - change incrementally). I've tried the INDIRECT function, but it does not work because when each sheet is sorted, the link is broken. Here is the link formula:

='C:\My Documents\My Books\[Book1.xls]Wk1'!K8

If I could get the "Wk1" to change and increment automatically, it would be the answer. I know I can get the current sheet name into a cell using this formula:

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

With that formula in any cell on Book1, it will show the correct week I want to change the link formula to, I just don't know how (or if you can) place the second formula (or a cell reference to the value resulting from the second formula) in the first formula (replacing Wk1).

Any help would be greatly appreciated.

CVinje

[DonkeyOte]

Whenever you're looking to generate "dynamic" links to other sheets / workbooks with formulae you're invariably looking at INDIRECT - to which there are noticeable drawbacks:

1 - INDIRECT is Volatile

2 - INDIRECT will not work with closed targets (ie if source file is closed you will get errors)

The 2nd issue can be avoided if you're prepared to use 3rd party add-in like Morefunc.xll and specifically INDIRECT.EXT function but still far from ideal.

In this instance... given you're using VBA to control this process why not as part of your routine run a section of code to replace Wk1 with Wk2 etc in your formulae references ?

One caveat however ... I'm still not sure I quite understand the point about:

I've tried the INDIRECT function but it does not work because when each sheet is sorted, the link is broken.

Can you elaborate ?

[CVinje]

When I say the link "breaks" when using the INDIRECT function I mean that it gives a #REF error. Here is the function I'm using to link from Book1 to Book2:

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

When I enter this in a cell on Book1 that I want linked to Book2, it does so correctly; however, when I sort my worksheet on Book1 the links then return #REF errors in the cell. Also, when reviewing the formula after sorting, this is the entry:

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

When I saw that (just now as of posting this), I simply put $A$1 instead of A1 and it seems to work - I have not yet completed checking it as I have 20 columns of 24 rows of information that the function must be placed into, and when using auto-fill it does not change the linked cell on Book2 (the L8) so I must do each manually (unless you know a faster way maybe?). It does seem this is the only way to accomplish what I want; however, now I have the issue of the other Book2 needing to be opened along with Book1 (I figure I can have a macro accomplish that - to automatically open Book2 when Book1 is opened), and my other problem is that when a new row of information is added to Book2, I have to manually paste and modify the INDIRECT formula in Book1 - and I will not be the primary end-user of this workbook system. Is there any way to make a more streamlined approach to adding a new INDIRECT function to Book1 that links to the new rows added to Book2?

P.s. - You mention adding coding in my macro that would automatically change the links from Wk1 to Wk2; that would be the ideal fix for me; however, the sheet being copied must have that action take place only on cells that have certain criteria (I don't want all the cells on the sheet to be changed, just certain ones). I have been provided a macro that accomplishes something like that (it clears cells that meet a given criteria, or populates a formula into those cells in another modification), but it may be overly complex to try addressing both potential solutions right now. I really don't have too much of a problem needing Book2 to be opened at the same time Book1 is opened (assuming I can have a macro do so automatically when Book1 is opened). If use of the INDIRECT function proves to be inadequate, perhaps then I will pursue obtaining assistance modifying my code (I don't know which solution would be easier / more effective). If you think that would be the most prudent course of action (instead of using INDIRECT), here is the code I referenced - there are actually two. The first clears cells based of the value in cell D1, the second populates a formula into cells based off cell D1:

Sub ClearCellCond()
Dim MyRange As Range, MyText As String
MyText = Range("D1").Value
LastRow = Cells(Rows.Count, "W").End(xlUp).Row
Set MyRange = Range("W8:W" & LastRow)
For Each c In MyRange
    If c.Value = MyText Then
        c.Offset(, -11).ClearContents
    End If
Next
End Sub

Sub PutWCond()
Dim MyRange As Range, MyText As String
MyText = Range("D1").Value
LastRow = Cells(Rows.Count, "W").End(xlUp).Row
Set MyRange = Range("W8:W" & LastRow)
For Each c In MyRange
    If c.Value = MyText Then
        c.Offset(, -11).FormulaR1C1 = "=IF(ISTEXT(RC[-1]),""W"","""")"
    End If
Next
End Sub

Thanks again for your help.

CVinje