Connect data from hundreds of closed workbooks to one workbook

Dear Scientists,

I have a simple problem but I really can't find an elegant solution (not ctrl + c and ctrl v).

Workbook "2014 Sales.xlsx" must have 12 worksheets, one per month, which one must have 31 columns that gets data from workbooks 2014 01 01.xlsx; 2014 01 02.xlsx; 2014 01 03.xlsx;... and so on, as externals links. And I must say that, I can NOT work with 31 different workbooks all open at the same time!

I know that, there is no way to make INDIRECT (....) read from closed workbooks. Period.

Is there a simple way to make that work? Maybe by using =vlookup(....)...

I did a small example, but it gives you an idea.

Thanks and keep it easy, literally. 2014 01 01.xlsx2014 01 02.xlsx2014 01 03.xlsx2014 Sales.xlsx

What about the 2 sheets you have in each day worrkbook? You probably want the sum of every sheets for one specific shop. Don't you?
Why don't you have all of this in only one workbook, even one sheet?
Or at least have a full month in only one sheet adding columns like State and date to the actual Shop and Sales columns.

this formula will update even if workbooks are closed. But It will take some time to do this.
Change the actual path in the formula to your real workbooks' location.

=IFERROR(VLOOKUP($A3,'C:\Data2\test\[2014 01 01.xlsx]NY'!$A$2:$B$5,2,FALSE),0)+IFERROR(VLOOKUP(A3,'C:\Data2\test\[2014 01 01.xlsx]CA'!$A$2:$B$5,2,FALSE),0)

You may want to try this macro that will open every files and extract the sum of the shop sales from every tabs it contains.
Look at the comments in the code for some specifics issues about this code.

Public Sub Lookup_Values()
'in order for this macro to work you must follow those rules:
'1. Files names must be structured as YYYY MM DD.xlsx (ex. 2014 05 15.xlsx)
'Months and days lower than 10 must have a 0 before the digit
'2. Put this workbook in the same folder as all the other files.
'3. The year is calculated from the tabs name in the code below
'4. The tabs name must be written as a data in the format MMM YY would be in your regional settings.
'   (ex. in english February 2014 would be Feb 14 but in french it would have been Fév 14)
'   I don't know what it will be in protuguese but it has to be that way because
'   I use the tab name in the month function to find the month and year of this tab.
Dim Sh_Temp As Worksheet, File_Name As String, C_ell As Range, Y_ear As String
Dim M_onth As String, D_ay As String, WB As Workbook, Sh_A As Worksheet
Dim F_ound As Range, S_hop As Range
'
Application.ScreenUpdating = False
'
For Each Sh_Temp In ActiveWorkbook.Worksheets
   Sh_Temp.Activate
   '
   For Each C_ell In Range("B2", Cells(2, Columns.Count).End(xlToLeft))
      'This section is creating the file name based on the tab's name and the number in row 2 of each file.
      Y_ear = Year(Sh_Temp.Name)
      If Month(Sh_Temp.Name) < 10 Then
         M_onth = "0" & Month(Sh_Temp.Name)
      Else
         M_onth = Month(Sh_Temp.Name)
      End If
      If C_ell < 10 Then
         D_ay = "0" & C_ell
      Else
         D_ay = C_ell
      End If
      '
      File_Name = Y_ear & " " & M_onth & " " & D_ay & ".xlsx"
      '
      If Dir(ActiveWorkbook.Path & "\" & File_Name) <> "" Then
         'This line open the specific file for this month and day
         Workbooks.Open Filename:=ActiveWorkbook.Path & "\" & File_Name
         '
         Set WB = ActiveWorkbook
         Sh_Temp.Activate
         '
         For Each S_hop In Range("A3", Cells(Rows.Count, 1).End(xlUp))
            'This line erases the original content of the target cell
            'If you don't want this to append, DELETE IT or COMMENT IT
            'In this case the values of the files will be added to existing amount in cell
            Cells(S_hop.Row, C_ell.Column) = 0
            'This section looks into every sheet in the file and adds the shop's sales
            For Each Sh_A In WB.Worksheets
               Set F_ound = Sh_A.Columns(1).Find(What:=S_hop, Lookat:=xlWhole)
               If Not F_ound Is Nothing Then
                  Cells(S_hop.Row, C_ell.Column) = Cells(S_hop.Row, C_ell.Column) + F_ound.Offset(0, 1)
               End If
            Next
         Next
         Workbooks(File_Name).Close False
      End If
      '
N_ext:
   Next
Next
Application.ScreenUpdating = True
End Sub

Hope this will help.