Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    linking worksheets (Excel 97)

    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.

  2. #2
    New Lounger
    Join Date
    Jun 2002
    Location
    Kansas
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking worksheets (Excel 97)

    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

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking worksheets (Excel 97)

    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.

  4. #4
    New Lounger
    Join Date
    Jun 2002
    Location
    Kansas
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking worksheets (Excel 97)

    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

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking worksheets (Excel 97)

    <img src=/S/puke.gif border=0 alt=puke width=60 height=15> - 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: <fileU://sharedcivil justice reformregressive hotline/regressive hotline stats.xls> ty [img]/forums/images/smilies/sad.gif[/img]|)

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking worksheets (Excel 97)

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

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: linking worksheets (Excel 97)

    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
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •