Create links with autofill

[=?windows-1257?Q?Linas_Petkevi=E8ius?=]

Hallo,

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
...........

 

[Eddy]

Autofill the [Week33.xls] part first, change all "]" to "]Sheet1!$B$4", and
then change all "[" to "=["

Hallo,

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
...........

 

[=?windows-1257?Q?Linas_Petkevi=E8ius?=]

Autofill the [Week33.xls] part first, change all "]" to "]Sheet1!$B$4", and

then change all "[" to "=["

Autofill works with Week33, but not with [Week33.xls]. Maybe there is solution like using INDIRECT(), but without opening linked files ? In excell 4.0 there was function EVALUATE(). Where is it now ? I have found some user created, but they don't work with external links, when they are closed ;(((

Please help !!!

 

[Eddy]

I am using Excel 2000, it fills the rest of the column if you type
[Week33.xls] in the first cell and [Week34.xls] the second. And then you
drag the two cells to down below, it would fill up the number [Week35.xls],
[Week36.xls], [Week37.xls] and so on.

Another stupid workaround is that if you construct your worksheet as below

Cell A1 -> "[Week"
Cell B1 -> 33
Cell C1 -> ".xls]Sheet1!$B$4"
Cell D1 -> A1 & B1 & C1
Copy entire column D
Replace all "[" to "=[" at column D

Autofill the [Week33.xls] part first, change all "]" to "]Sheet1!$B$4", and
then change all "[" to "=["

Autofill works with Week33, but not with [Week33.xls]. Maybe there is

solution like using INDIRECT(), but without opening linked files ? In excell
4.0 there was function EVALUATE(). Where is it now ? I have found some user
created, but they don't work with external links, when they are closed
(

 

[Bill Manville]

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