Index() & Indirect() on a closed Excel

Question

I have a few closed Excel workbooks, which I would be willing to access with =INDEX(MATCH) without openning them.

Unfortunately, I want to refer to them indirectly, thus based on the current sheet I am in.

E.g. - if I am in sheet -> then file path:

AAB -> 'C:\Users\vityata\Desktop\[AAB]Test'!$A:E

BBC -> 'C:\Users\vityata\Desktop\[BBC]Test'!$A:E

It seems like a trivial task, but the only options to do something like this seem to be the following:

Open the sheet and then use Indirect() (but I do not want to open the sheet)
Use VBA code (but it should be somehow reusable and really last resort)
Use data connection (I do not want it)
Use INDIRECT.EXT (but I do not want 3. party add-ins)

I have checked similar problems here: How to referencing value in closed excel workbook by formula incl. variable sheetname? but the first solution is rather too colmplicated to do it.

Any new ideas or best practices?

Update So far I have achieved the following with VBA (although I would love to have a non-VBA solution):

Option Explicit

Public Sub VlookupClosedWb(Optional strLookFor As String = "Erteilung Baugenehmigung", _
                        Optional strSheet As String = "APF", _
                        Optional lngColIndex As Long = 2)

    Dim strRange As String

    strRange = "'" & ThisWorkbook.Path & "\[" & strSheet & ".xlsx]" & "Monatsbericht'!$A:$E"
    ActiveCell.Formula = "=VLOOKUP(""" & strLookFor & """," & strRange & "," & lngColIndex & ",0)"

End Sub

Public Function fVlookupClosedWb(Optional strLookFor As String = "Erteilung Baugenehmigung", _
                        Optional strSheet As String = "APF", _
                        Optional lngColIndex As Long = 2)

    Dim strRange    As String

    strRange = "'" & ThisWorkbook.Path & "\[" & strSheet & ".xlsx]" & "Monatsbericht'!$A:$E"

    fVlookupClosedWb = WorksheetFunction.VLookup(""" & strLookFor & """, " & strRange & ", " & lngColIndex & ", 0)

End Function

Long story short - the Sub works as expected, providing exactly what I want in the active cell. The Function does not work. Any ideas how to make it work? I suppose that it does not work, because I am abusing a bit WorksheetFunction.VLookup, but I cannot pass a range without openning the other workbook. Ideas?

Maybe try showing your code based on the linked question so others can see what you did and might be able to spot where you went wrong. — FreeMan
@FreeMan - Just done it. I did not want to work with VBA and it was actually my last resort... — Vityata
Also, did you try the "crazy sort of method" linked in Choirbean's answer? Looks like that might just work for you. — FreeMan
@FreeMan - tried it, but it is too complicated. I would rather start openning the files and resolve the problem with Indirect than doing this. — Vityata

Vityata Vityata · Accepted Answer · 2017-07-25T11:52:55

After some tries, I found out that the way to use some formulas on a closed excel workbook, without referring to the path is by using the Sumproduct() function.

E.g.: If you want to use Sumproduct() as an alternative of Sumifs() this is how it looks like with two conditions:

=SUMPRODUCT(((B2:B6=C2)*1)*(A2:A6=D2))
*SUMPRODUCT(((B8:B13=C8)*1)*(A8:A13=D8))
*SUMPRODUCT(((B16:B20=C16)*1)*(A16:A20=D16))

I have put some more examples of it here.

Index() & Indirect() on a closed Excel

1 Answers