linking worksheets (Excel 97)

Topic

New Reply

Does anyone have a solution to this one?

I wish to create a document that is read only which contains 2 worksheets from 2 different documents. the data needs to be linked (an exact replica of what’s in the parent documents). When I attempt to copy the sheets to a new book, there is no linking that is done. Similarly when I have tried to paste special, there isn’t enough memory and excel stops responding.

I am open to any suggestions… especially considering my rationale may not be correct in solving this. The end result, a copy of my data, sitting on our shared network, that other employees can open and view but not alter. It needs to update automatically either when the viewer opens the document or when the parent documents are saved.

Any ?, just ask.. Any answers, just tell. Thanks. Jenn.

Reply | Quote

Replies

You could build the links by hand using:
='[wbname.xls]Sheet1′!$A$3
and then protect all of the formulas in the workbook to keep end-users from changing them.

HTH

Jeff

Reply | Quote

Ok, after several variations, I can’t seem to reference the entire sheet and I have to include the path also. So right now it looks like such:

=’U:SharedCivil Justice ReformRegressive Hotline[Regressive Hotline Stats.xls]CALL TALLY’!Print_Area

Unfortunately, this only reproduces what’s in A1.

Also, when i open up the destination document, it asks me if i want to update the document from the document. How can this be changed… i want the document to only update when the parents documents are saved.
Further suggestions appreciated.

Reply | Quote

Yes, the formula will only work for the data in cell A1. You would need to replicate the formula for each cell you have data in. Also, each time it is updated, it will pull fresh data from the parent documents…changed or not.

Another option you might want to look at instead is the Get External Data feature. If you have a large amount of data, it will probably work smoother. Set refresh on open and set a refresh timer so that it refreshs as often as you think necessary. There is really no way to make it watch for changes and only refresh at that time (at least that I know of).

HTH

Jeff

Reply | Quote

– Oh well! I thought you might say that… way too many cells to individually reference… Perhaps another route is to automate the copying of the sheets to a new book and sending it to an email group. (the original/parent files are only updated once per day). That would bypass the entire linking issue. The hitch is that i don’t know how to do it… probably a macro… AND I don’t send the document to everyone, the email simply consists of a file link like what follows and general instructions on opening the file: ty |)

Reply | Quote

You should be able to create one reference in A1, then Fill it to the other cells to create references there.

Reply | Quote

While linking’s pretty straight forward, if you do that with a spreadsheet that you want to give others access to is they’ll have to deal with Excel wanting to update the links every time the target spreadsheet is opened (unless the users have turned this feature off). If anyone answers yes to the ‘update links’ prompt, Excel’s going to go looking for the source spreadsheets and that will delay the opening – and if the spreadsheet their using has been moved, there’ll be even bigger problems when Excel can find the source spreadsheets.

Since these are to be read-only spreadsheets, why not set up a simply copy the formats and values from the source spreadsheets to the target spreadsheet. This will keep the appearance and minimises the size of the target (which speeds up loading across networks).

You could even use a macro to do this. For example, if your source data is on Sheet1 of Source1.xls and Sheet1 of Source2.xls, and you want to replicate their appearance in Sheet1 and Sheet2, respectively, of Target.xls :

Sub GetData1()
Windows(“Source1”).Activate
Sheets(“Sheet1”).Select
Cells.Select
Selection.Copy
Windows(“Target”).Activate
Sheets(“Sheet1”).Select
Selection.PasteSpecial Paste:=xlFormats
Selection.PasteSpecial Paste:=xlValues
Range(“A1”).Select
Windows(“Source2”).Activate
Sheets(“Sheet1”).Select
Cells.Select
Selection.Copy
Windows(“Target”).Activate
Sheets(“Sheet2”).Select
Selection.PasteSpecial Paste:=xlFormats
Selection.PasteSpecial Paste:=xlValues
Range(“A1”).Select
End Sub

You could put this macro into any spreadsheet (preferably not the target one – otherwise the target’s users will get ‘macro warnings’). I haven’t included any error checking etc in the macro, so you’d need to make sure that both the source and target spreadsheets are open and that all the references are correct.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote