Howdy, not quite sure what we're up to here, but one can use vba to dynamically update the file link on a calculation. I cooked up the following example:
Book1 |
---|
|
---|
| A | B | C | D | E | F |
---|
1 | 08-02 | test2 | | | | 08-02 |
---|
2 | | | | | | 08-03 |
---|
|
---|
Basically I used a data validation list in A1 of column F values. When you you change a1 in XL 2000, you'll inspire Excel to recalc. Accordingly I used a worksheet_change procedure to replace the dates in all of the workbooks formulae. The procedure I used is as follows:<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
'Macro recorded by Nate
Dim Worksheet As Worksheet, cl As Range, frm As String
Dim z As String, o As Integer, s As Integer, FrmCls As Range
If Target.Address = "$A$1" Then
For Each Worksheet In ThisWorkbook.Worksheets
Set FrmCls = Nothing
On Error Resume Next
Set FrmCls = Worksheet.[a1].SpecialCells(xlFormulas)
If FrmCls Is Nothing Then GoTo 1
For Each cl In FrmCls
frm = cl.Formula
If InStr(frm, "[") Then
o = InStr(cl.Formula, "[")
s = InStr(cl.Formula, "]")
z = Mid(cl.Formula, o + 1, s - o)
cl.Replace What:=z, _
Replacement:=Format([a1], "mm-yy") & _
" Trial Balance.xls]", _
MatchCase:=False, lookat:=xlPart
End If
Next cl
1:
Next Worksheet
End If
End Sub</pre>
This has to go in the correct worksheet's module. A quick way to get there is to right-click on your worksheet tab, click
view code and paste the code above in.
Perhaps this may be of help. Have a nice weekend.
_________________
Cheers,<font size=+2><font color="red">
Nate<font color="blue"> Oliver</font></font></font>
This message was edited by nateo on 2002-09-13 13:47