Link to cell in another workbook

[flaviooooo]

Hey,

I have a list of usernames as such:

John Davies
Elmer McDonald
Alberto Vicenzo
...

Every month, excel workbooks are created via a VBA macro that have the names of these users. So we have a book called John Davies.xls, Elmer McDonald.xls etc etc

Now: in each of these sheets, several calculations are done and a total amount is listed at the bottom.

The objective now is to add this total amount into the basic list of all usernames.
If I type it in completely, it works as follows:

='[201104_JOHN DAVIES.xls]Sheet1'!$T$39

But since it is quite an extensive list (that might change over time), I would prefer to have a formula that would check the cell where the name is filled in... and use that field to look for the filename. Unfortunately, this gives me a #REF error:

='[201104_" & B2 & ".xls]Sheet1'!$T$39

Any ideas on how to accomplish this?

thanks!

 

[mintjulep]

Use the INDIRECT() function.

 

[flaviooooo]

Thanks for the response mintjulep, but I think that function requires the other workbook to be open.

Since it's approx 300 usernames, I don't feel like opening all of them ;-)

 

[SkipVought]

Every month, excel workbooks are created via a VBA macro that have the names of these users. So we have a book called John Davies.xls, Elmer McDonald.xls etc etc

THIS is the place in the process where you could most easily perform this aggregation, just before you spawn skads of workbooks.

Revisit your initial VBA process.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[flaviooooo]

Thanks Skip.

Unfortunately, the files are created at the beginning of the month and then filled in gradually during the month.
It's only at the end of the month, we'd like to get the results.

I'm starting to believe that I will need to create a new VBA script to extract these values, instead of a simple formula?

 

[SkipVought]

Use a Query.

I often create database functions to extract and return a piece of data based on one or more arguments. This example queries a sheet (table) within the calling workbook (ThisWorkbook). Based on an ITEM id and ad FLD field designation it returns a value from the table for that item...

Function fLotItemValue(ITEM As Long, FLD As String) As String
    Dim sConn As String, sSQL As String
    Dim rst As ADODB.Recordset, cnn as ADODB.Connection
     Dim sPath As String, sDB As String
    
    sPath = ThisWorkbook.Path
    
    sDB = Split(ThisWorkbook.Name, ".")(0)

    Set cnn = New ADODB.Connection
    cnn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
           "DriverId=790;" & _
           "Dbq=" & sPath & "\" & sDB & ".xlsm;" & _
           "DefaultDir=" & sPath & ""
   
    Set rst = New ADODB.Recordset

    sSQL = sSQL & "SELECT [" & FLD & "]"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "FROM [tblLotItem]"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "WHERE ITEM=" & ITEM
            
    Debug.Print sSQL
        
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
                          
    On Error Resume Next
                          
    rst.MoveFirst

    If Err.Number = 0 Then
        fLotItemValue = rst(0)
    Else
        fLotItemValue = rst(0)
    End If
    
    rst.Close
    
    
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]