Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: linking (2000)

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    linking (2000)

    I have 8 separate workbooks. I want to create 1 workbook with 8 worksheets. I want the actual data to be in the 1 workbook with 8 worksheets but when the original worksheets are updated, I want the workbook with 8 worksheets to update automatically. The 8 separate workbooks contain graphs. Is there a way to do this? I thought hyperlinks but I don't want the hyperlink on the 8 worksheets - I want the actual data. Thanks for your help.

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

    Re: linking (2000)

    Select and Copy a worksheet. Go the the workbook where you want the copy and select the sheet which is to contain the copy. On the Edit menu click on "Paste Special". In the dialog box, click on the Paste Links button. Repeat for the other 7 sheets.
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: linking (2000)

    You could create external links on each of the sheets:
    something like in A1
    ='C:[OtherBook.xls]Sheet1'!A1

    Copy as much as you need. These will automatically update.

    For the charts you might use the camera tool to take a "picture" of the sheet and create this external link.

    The downside: It might be slow if the range of calcs is very large. In this case it might be faster to have a workbooks open macro to just open each of the 8 workbooks and "copy" the sheet info whenever the combined workbook is opened

    Steve

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking (2000)

    I highlighted and copied the worksheet and then went into the workbook and selected worksheet 1 and did paste special and clicked on the paste link button but all I got was a worksheet that had a lot of 0s. The worksheet I am copying from is a sheet of graphs.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking (2000)

    The data is all graphs. I took a snapshot and when I went to paste link, it said picture too large and will be truncated.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: linking (2000)

    This technique grabs cell contents not charts.
    Select the range in the source, press the "camera tool" {see Joh Walkenbach for more info on camera tool)
    Then go to the desination file and select a range to start the "picture"

    If the range is too large, you might have to take individaul "snapshots" of the charts.
    Steve

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking (2000)

    I tried tryping in the external link in A1 like this:
    ='N:adminmonthly enrldashboardtest folder[hs-csgdashboard_sep04.xls]'!melani pg1'!A1:bf27

    I get a 0 in a1. Am I doing something wrong??

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

    Re: linking (2000)

    Enter the formula in A1 without the ":bf27" on the end. Then copy it down and over to BF27.
    Legare Coleman

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking (2000)

    It just copies in 0s. Maybe this isn't possible with the spreadsheets I am trying to link.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: linking (2000)

    The cell links reference the cell's value. If the cell is blank the cell link will display "0" (the value of blank cell = 0)

    If you want to link a picture then you can add a picture, select the picture and then in the formula bar enter the entire range reference (this is what the camera tools does). In this case you link to a picture of a range, not the individual cell value

    Steve

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking (2000)

    Is there any data in what you're trying to copy? Or is it all graphs? Where is the information that is used to create the graphs?

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking (2000)

    The data is in a separate worksheet. I don't want the data -just the graphs.

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking (2000)

    Then could you just copy all the workbooks into 8 sheets of a new workbook by right clicking the tabs, choosing Move or Copy, and copying them all into the same book? The references would stay the same, and when you updated your information in what I assume is the 9th workbook, the graphs should get updated as well.

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking (2000)

    What about if I put hyperlinks to all these separate spreadsheets on one sheet. Do I have to open all the hyperlinks and print or is there some way I can print all the hyperlinks at once without opening each one. I can bookmark each of the hyperlinks to go to the right worksheet in each hyperlink and give it a range too. Maybe there is a macro that once I have all my hyperlinks set up on one spreadsheet. the macro could print them all. Is this possible?

  15. #15
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking (2000)

    Yes - that worked. Now will those spreadsheets update when the original 8 spreadsheets are updated.... Thanks so much for your help......

Page 1 of 2 12 LastLast

Posting Permissions

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