Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Rockford, Illinois, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking multiple worksheets (Excel 97 SR2)

    If I have 10 worksheets and am using an 11th sheet to summarize the 10 sheets. For each summary group, I have to pull the information from the same cell on every worksheet. Right now I point and click in the formulas to establish the links. This is very time consuming. Since I am pulling the information from the same cell in every worksheet, is there a function I can use to look at all the worksheets and sum the values from the cells in each worksheet? Thanks in advance.

  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 multiple worksheets (Excel 97 SR2)

    <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=66592&page=&v iew=&sb=&o=&vc=1#Post66592>This Thread</A> might give you an idea how to do it.
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Rockford, Illinois, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking multiple worksheets (Excel 97 SR2)

    Thanks. I will give it a try.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Linking multiple worksheets (Excel 97 SR2)

    Legare's suggestion will work even if you are using multiple workbooks, using a syntax such as: =INDIRECT("[Source1.xls]Sheet1!$c$1")+INDIRECT("[Source2.xls]Sheet1!$c$1")+INDIRECT("[Source3.xls]Sheet1!$c$1") ... etc. However, you will get #ERR! results if the source workbooks are not opened, and update Links does not work.

    For flexibility, you canto separate out the Workbook name, Sheet name and cell address components into cells on the Target workbook so you can Find&Replace to create different versions of the formulas. For example, for reference to one workbook, where the formula and components are in cells A11:
    A1 B1 C1 D1
    =INDIRECT(" ' "&B1&C1&" ' ! "&D1) '[Source1] 'Sheet1 '$C$1
    note the use of quote apostrophe quote, and quote apostrophe exclamation quote to handle Workbook and Worksheet names with spaces in them (spaces added for reading clarity).

    The advantage of this obscure approach is that it is then easy to copy and modify the =INDIRECT formula to extract different sets of data from the different workbooks and worksheets.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    New Lounger
    Join Date
    Sep 2001
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking multiple worksheets (Excel 97 SR2)

    I had a similar problem, summarising up to 15 or more worksheets onto one sheet in the same workbook. I created a little macro that took the contents from a cell and inserted it beside the name of the sheet it came from as the names and number of the sheets varied from workbook to workbook.
    See what you think and I hope it helps.

    Sub SummariseIt()
    '
    ' Macro1 Macro
    ' Macro recorded 17/11/2001 by Peter Morris
    '

    Dim MyRange
    Dim FormForMe
    Dim MyRangeName
    Dim NewSheet

    If Worksheets(1).Name = "Summary" Then
    Call DoNames(ws)
    Else
    MsgBox "No Summary"
    'new.sheet
    Set NewSheet = Worksheets.Add
    NewSheet.Name = "Summary"
    Call DoNames(ws)

    End If

    End Sub

    Sub DoNames(bs)
    Dim ws
    Dim NoSheets As Integer
    NoSheets = 0
    For Each bs In Worksheets
    NoSheets = NoSheets + 1
    Worksheets("Summary").Cells(NoSheets, 1).Value = bs.Name
    Worksheets("Summary").Cells(NoSheets, 2).Value = "='" & bs.Name & "'!c36"
    Worksheets("Summary").Cells(NoSheets, 3).Value = "='" & bs.Name & "'!c38"
    Worksheets("Summary").Cells(NoSheets, 4).Value = "='" & bs.Name & "'!m29"
    Next bs

    MsgBox "Summary done"

    End Sub

Posting Permissions

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