Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Greetings!

    Is there a way to set up an array formula in workbook A to calculate directly on the cells in workbook B? As long as workbook B is open, all is well, but as soon as it's closed, all the array formulas display #VALUE. I can put run the array formulas in workbook B, and reference them from A, but this will take a LOT of extra work...

    Thanks!
    Angela

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    You could use something similar to this:

    [codebox]Sub LinkToClosedBook()
    GetValuesFromWorkbook "C:", "MyClosedWoorkBook.xls", _
    "Sheet1", "A1:J100"
    End Sub

    Sub GetValuesFromWorkbook(strPath As String, _
    strName As String, strName, strRange As String)
    With ActiveSheet.Range(strRange)
    .FormulaArray = "='" & strPath & "\[" & strName & "]" _
    & strName & "'!" & strRange
    .Value = .Value
    End With
    End Sub[/codebox]

    Place the code in a standard Module of the workbook which requires the data and place the "closed" workbook on the C: drive ( for this example. You can now reference your formulas to the worksheet that this data writes too.

    This code references range A1:J100 on sheet1 in MyClosedWoorkBook.xls but can be run from any sheet in your active book.

    HTH
    Jerry

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, that's a start; my problem is that I have about 30 different closed workbooks that all have data that I want to bring into this workbook. Would I have to create 30 subroutines then? At this point I'm thinking I may just create a tab in each of the closed workbooks that runs the array formula, and then have this one just capture the linked data.

  4. #4
    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
    What is the array formula like?

    When I use and create array formulas (either explicitly with ctrl-shift-enter or implicitly with sumproduct in XL2002) they work with external workbooks even when the workbooks the arrays read from are closed...

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm using the Double Lookups formula from here: http://www.cpearson.com/excel/TablesAndLookups.aspx

    Do you think it's the OFFSET function maybe??

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You don't need an array formula to do a double lookup; you can use something like this, using Chip's sample workbook:

    =INDEX(C:\Other\[Test.xls]Sheet1'!$B$2:$F$6,MATCH(E82,C:\Other\[Test.xls]Sheet1'!$A$2:$A$6,0),MATCH(E83,C:\Other\[Test.xls]Sheet1'!$B$1:$F$1,0))

    where C:\Other\Test.xls and Sheet1 are the workbook and worksheet containing the table referred to.

    This formula will work even if the other workbook is closed.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    oh-my-word that's so much simpler!

    Once again, Hans, you've saved the day!

    Angela

Posting Permissions

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