Linas Petkevičius wrote:
I have Link to external spreadsheet, for example, formula in A1 is "=[Week32.xls]Sheet1!$A$1".
Is it posible to use autofill and receive such formulas:
=[Week33.xls]Sheet1!$B$4
=[Week34.xls]Sheet1!$B$4
=[Week35.xls]Sheet1!$B$4
No.
For one thing the first had $A$1 and the others $B$4 g
But even if the first was $B$4 which you no doubt meant, AutoFill will only change the cell
reference, and then only if the $ are omitted.
If the other sheets would all be open you could do it using INDIRECT
=INDIRECT("[Week" & ROW()+31 & ".xls]Sheet1!$B$4")
which you could fill or copy down.
More likely they will not all be open.
In that case I would use a macro to fill down.
Make sure the files to be referenced are all open or are all closed
Select the range to fill, with the formula to "fill" in the first cell and run this :
Sub FillBookDown()
' fill down formula of form =[Weeknn.xls]Sheet1!$B$4 changing the week number
Dim C As Range
Dim lRow As Long
Dim iStartNo As Integer
Dim iChar As Integer
Dim stForm As String
Const iDigits=2 ' number of digits in the filename
If Selection.Rows.Count=1 Then Exit Sub ' nothing to do
stForm = Selection.Range("A1").Formula
iChar = InStr(LCase(stForm),".xls")
' 2 digits before .xls
iStartNo=Val(Mid(stForm,iChar-iDigits,iDigits))
Selection.FillDown
For lRow=2 To Selection.Rows.Count
Selection.Rows(lRow).Replace Mid(stForm,2,iChar-1-iDigits) & iStartNo, _
Mid(stForm,2,iChar-1-iDigits) & iStartNo+lRow-1, xlPart
Next
End Sub
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup
|