Create links with autofill

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Linas Petkevičius - 26 Aug 2004 06:34 GMT

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

Reply to this Message

Eddy - 26 Aug 2004 07:24 GMT

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

"Linas Petkevi?ius" <lincas@post.5ci.lt> ?????
news:#7HTv5yiEHA.636@TK2MSFTNGP12.phx.gbl...

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

Reply to this Message

Linas Petkevičius - 26 Aug 2004 07:47 GMT

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

Reply to this Message

Eddy - 26 Aug 2004 08:06 GMT

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

"Linas Petkevi?ius" <lincas@post.5ci.lt> ?????
news:ez#kYiziEHA.3664@TK2MSFTNGP11.phx.gbl...

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

Reply to this Message

Bill Manville - 26 Aug 2004 08:22 GMT

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

Reply to this Message