References to external workbooks not working unless they are open

[helamansanchez]

Quick question regarding some references I have to external workbooks.

The formula in question is:

=VLOOKUP("*"&B5,INDIRECT('Links to Workbooks'!$I$3),'Links to Workbooks'!$C$4,FALSE)

• Where the lookup value B5 contains "Total Revenue" and is what I am hoping to match using VLOOKUP in other files;
• 'Links to Workbooks'!$I$3 contains a CONCATINATE function linking together the file path name for the workbook from which to pull data based off of:
  • C3, which returns a certain customer name depending on which program is chosen from a dropdown menu (see below)
  • Summary!C2, which has a dropdown menu to choose which year we want to look at
  • Summary!B2, which has a dropdown menu to choose which program we want to look at, and determines the value of C3 (above)

So, the function

=CONCATENATE("'C:\Users\zjf62t\Documents\Program Profitability\1 Summary\[Summary ",C3," ",Summary!C2,".xlsx]",Summary!B2,"'!$B$1:$JZ$100")

returns the value

'C:\Users\zjf62t\Documents\Program Profitability\1 Summary\[Summary Customer1 2016.xlsx]Program1!$B$1:$JZ$100

if we have selected Program1 for Customer1 for the year 2016 as the parameters to analyze;​

• 'Links to Workbooks'!$C$4 is

=MATCH(A4,INDIRECT(I2),0)-1

which takes the phrase in A4 specifying whether we are looking for a specific month or the YTD where​

• I2 is a CONCATENATE function very similar to the one I described for cell I3 above, which returns the exact same file path, simply with the range at the end changed to include only the top row of the table

This returns the column to look for in the VLOOKUP
​

The problem I keep running into is that this VLOOKUP function returns #REF unless the workbook that it is pulling from (in the example I used above, this would be the workbook called Summary Customer1 2016) is open.

The value of cell C4 in the Links to Workbooks tab also appears as #REF .

I have a vague idea that the issue might have to do with the INDIRECT function that I use both in cell C4 and in my VLOOKUP formula, but I don't know why or how to work around it.

Any ideas are appreciated

 

[Tom Urtis]

If I understand the root of your question, the INDIRECT function does not read from a closed workbook, which is why your VLOOKUP function with its INDIRECT function in it would never work for you when referring to a closed workbook.

 

[helamansanchez]

If I understand the root of your question, the INDIRECT function does not read from a closed workbook, which is why your VLOOKUP function with its INDIRECT function in it would never work for you when referring to a closed workbook.

Hi Tom, thanks for responding.

Yes I suspected the INDIRECT function is what was doing it....

Is there any way to make the function work with a closed workbook? Or an alternative function that would do the same thing as INDIRECT while working with closed workbooks?

 

[Tom Urtis]

No way for INDIRECT to work with a closed workbook.
An alternative sans VBA is the general syntax
=VLOOKUP(A1,'c:\yourfilepath\[test1.xlsx]Sheet1'!A1:B100,2,0)
Of course with VBA the options are numerous.

 

[helamansanchez]

That would work, except the file name that needs to be referenced is dynamic and depends on what two parameters you choose as the program and year as explained in the second bullet point above. This is why I have the file path linked rather than hard-typed.

I am fine with using VBA...do you know of any code that could help me do what I need?

 

[Tom Urtis]

If it were me, I'd publicly declare the 3 string variables in a standard module, example:

Public strVariable1$
Public strVariable2$
Public strVariable3$

Then in the worksheet module I'd place these 2 events:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$B$2" Or Target.Address = "$C$2" Then
If Len(Target.Value) <> 0 Then
strVariable2 = Range("B2").Value
strVariable3 = Range("C2").Value
End If
End If
End Sub

Private Sub Worksheet_Calculate()
strVariable1 = Range("C3").Value
End Sub

From there, it's a simple matter of constructing your macro to open the workbook using the variables so-defined, do to it what you need to do programmatically, then close (and optionally save) it programmatically.

 

[annieexcel]

Hi there!! I had seen your thread before and was just looking through the stickied add in thread. I saw this about the "Morefunc" add in. Not sure if this is at all helpful but I just thought I'd point it out:

[FONT=&quot]A free library of 66 new worksheet functions for Excel 95 or above, written in C++, covering many categories : text, maths, statistical, information, lookup and matrix functions. For instance : INDIRECT.EXT (an INDIRECT-like function working with closed workbooks), NBTEXT (spells out numbers in 13 different languages), VSORT (sorts an array with up to 14 sort keys) and so on.[/FONT]