A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Create links with autofill



 
 
Thread Tools Display Modes
  #1  
Old August 26th, 2004, 06:34 AM
Linas Petkevičius
external usenet poster
 
Posts: n/a
Default Create links with autofill

SGFsbG8sDQoNCkkgaGF2ZSBMaW5rIHRvIGV4dGVybmFsIHNwcm VhZHNoZWV0LCBmb3IgZXhhbXBs
ZSwgZm9ybXVsYSBpbiBBMSBpcyAiPVtXZWVrMzIueGxzXVNoZW V0MSEkQSQxIi4gSXMgaXQgcG9z
aWJsZSB0byB1c2UgYXV0b2ZpbGwgYW5kIHJlY2VpdmUgc3VjaC Bmb3JtdWxhczoNCg0KDQo9W1dl
ZWszMy54bHNdU2hlZXQxISRCJDQNCj1bV2VlazM0Lnhsc11TaG VldDEhJEIkNA0KPVtXZWVrMzUu
eGxzXVNoZWV0MSEkQiQ0DQouLi4uLi4uLi4uLg0K

  #2  
Old August 26th, 2004, 07:24 AM
Eddy
external usenet poster
 
Posts: n/a
Default

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

"Linas Petkevičius" ?????
...
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
...........



  #3  
Old August 26th, 2004, 07:47 AM
Linas Petkevičius
external usenet poster
 
Posts: n/a
Default

PiBBdXRvZmlsbCB0aGUgW1dlZWszMy54bHNdIHBhcnQgZmlyc3 QsIGNoYW5nZSBhbGwgIl0iIHRv
ICJdU2hlZXQxISRCJDQiLCBhbmQNCj4gdGhlbiBjaGFuZ2UgYW xsICJbIiB0byAiPVsiDQo+IA0K
DQogICAgQXV0b2ZpbGwgd29ya3Mgd2l0aCBXZWVrMzMsIGJ1dC Bub3Qgd2l0aCAgW1dlZWszMy54
bHNdLiBNYXliZSB0aGVyZSBpcyBzb2x1dGlvbiBsaWtlIHVzaW 5nIElORElSRUNUKCksIGJ1dCB3
aXRob3V0IG9wZW5pbmcgbGlua2VkIGZpbGVzID8gSW4gZXhjZW xsIDQuMCB0aGVyZSB3YXMgZnVu
Y3Rpb24gRVZBTFVBVEUoKS4gV2hlcmUgaXMgaXQgbm93ID8gSS BoYXZlIGZvdW5kIHNvbWUgdXNl
ciBjcmVhdGVkLCBidXQgdGhleSBkb24ndCB3b3JrIHdpdGggZX h0ZXJuYWwgbGlua3MsIHdoZW4g
dGhleSBhcmUgY2xvc2VkIDsoKCggDQoNCiAgICBQbGVhc2UgaG VscCAhISENCg0K

  #4  
Old August 26th, 2004, 08:06 AM
Eddy
external usenet poster
 
Posts: n/a
Default

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" ?????
...
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 !!!





  #5  
Old August 26th, 2004, 08:22 AM
Bill Manville
external usenet poster
 
Posts: n/a
Default

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Quick links for Office 2003 djohnson Setup, Installing & Configuration 1 April 29th, 2004 10:36 PM
Links in XP missing but not in 2000 Rob Links and Linking 0 March 6th, 2004 09:41 PM
contains one or more links that cannot be updated... Gordon Links and Linking 0 February 6th, 2004 06:10 PM


All times are GMT +1. The time now is 12:09 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.