Array formula that references a linked workbook?

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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??

Reply | Quote

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:OtherTest.xls and Sheet1 are the workbook and worksheet containing the table referred to.

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

Reply | Quote

oh-my-word that’s so much simpler!

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

Angela

Reply | Quote