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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Create links with autofill
SGFsbG8sDQoNCkkgaGF2ZSBMaW5rIHRvIGV4dGVybmFsIHNwcm VhZHNoZWV0LCBmb3IgZXhhbXBs
ZSwgZm9ybXVsYSBpbiBBMSBpcyAiPVtXZWVrMzIueGxzXVNoZW V0MSEkQSQxIi4gSXMgaXQgcG9z aWJsZSB0byB1c2UgYXV0b2ZpbGwgYW5kIHJlY2VpdmUgc3VjaC Bmb3JtdWxhczoNCg0KDQo9W1dl ZWszMy54bHNdU2hlZXQxISRCJDQNCj1bV2VlazM0Lnhsc11TaG VldDEhJEIkNA0KPVtXZWVrMzUu eGxzXVNoZWV0MSEkQiQ0DQouLi4uLi4uLi4uLg0K |
#2
|
|||
|
|||
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
|
|||
|
|||
PiBBdXRvZmlsbCB0aGUgW1dlZWszMy54bHNdIHBhcnQgZmlyc3 QsIGNoYW5nZSBhbGwgIl0iIHRv
ICJdU2hlZXQxISRCJDQiLCBhbmQNCj4gdGhlbiBjaGFuZ2UgYW xsICJbIiB0byAiPVsiDQo+IA0K DQogICAgQXV0b2ZpbGwgd29ya3Mgd2l0aCBXZWVrMzMsIGJ1dC Bub3Qgd2l0aCAgW1dlZWszMy54 bHNdLiBNYXliZSB0aGVyZSBpcyBzb2x1dGlvbiBsaWtlIHVzaW 5nIElORElSRUNUKCksIGJ1dCB3 aXRob3V0IG9wZW5pbmcgbGlua2VkIGZpbGVzID8gSW4gZXhjZW xsIDQuMCB0aGVyZSB3YXMgZnVu Y3Rpb24gRVZBTFVBVEUoKS4gV2hlcmUgaXMgaXQgbm93ID8gSS BoYXZlIGZvdW5kIHNvbWUgdXNl ciBjcmVhdGVkLCBidXQgdGhleSBkb24ndCB3b3JrIHdpdGggZX h0ZXJuYWwgbGlua3MsIHdoZW4g dGhleSBhcmUgY2xvc2VkIDsoKCggDQoNCiAgICBQbGVhc2UgaG VscCAhISENCg0K |
#4
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 08:41 PM |
contains one or more links that cannot be updated... | Gordon | Links and Linking | 0 | February 6th, 2004 05:10 PM |