interrogating closed workbooks

[david763]

Hello, I'd prefer formula, vba, power query in that order for this problem if possible...
I need to interrogate a series of closed workbooks - the name of the workbooks are in a range of cells in the active workbook. The list will grow over time.
I need to pull and count the number of records which match multiple criteria (eg belonging to particular department, not closed and criticality) - all of which are in different fields of the target workbooks.
INDIRECT doesn't work on closed workbooks - at this point I can't find anything that does. I would need a formula for closed workbooks able to count multiple matching criteria.
If power query - I haven't used this a lot - I'd need a way of avoiding duplicating connections if they already exist.
Thanks in advance...

 

[Eric W]

What do the closed workbooks look like? Are they all formatted the same, same sheet names, same location of data? If the data is available, would a SUMIFS do the trick?

 

[david763]

What do the closed workbooks look like? Are they all formatted the same, same sheet names, same location of data? If the data is available, would a SUMIFS do the trick?

Thank Eric, good question.
They are extracted files from external database, formatted as Excel 2007 Data. The sheet name is always the same, column headings and nature of information is always the same.
I will look into SUMIFS in the meantime... thanks

PS the files are just data - no named ranges, no tables, etc...

 

[david763]

I went down the SUMIFS / COUNTIFS track and still doesn't work on closed workbook.
However, SUMPRODUCT works a treat when the path, filename, sheet and reference are all hard coded in the formula - it goes pear-shaped when I try to build the same information from the cell references - either #REF or 0 (zero) results...

 

[Eric W]

What you could do is set up a macro to do this. The macro would create the formula from the cell references, then place it on the sheet. Once it calculates, you grab the value and save it. Then you loop through your list of workbooks and repeat. If this seems reasonable, please show the formula that works, tell us where the list of workbooks is, and where you want the results placed.

I'm out of town at the moment, so I don't know if I'll be able to work on it for a few days.

 

[david763]

What you could do is set up a macro to do this. The macro would create the formula from the cell references, then place it on the sheet. Once it calculates, you grab the value and save it. Then you loop through your list of workbooks and repeat. If this seems reasonable, please show the formula that works, tell us where the list of workbooks is, and where you want the results placed.

I'm out of town at the moment, so I don't know if I'll be able to work on it for a few days.

Thanks Eric.

Details as follows:

RESULTS:
- filenames of closed workbooks are listed in BI13:BI16 of the active workbook (I will change to named range later)
- the result will be put in 3 cells to the right (ie BL13:BL16)

CLOSED WORKBOOKS:
- path, always the same, "\\server\common folder\path"
- folder, variable (based on range of cells), "2024 R"
- filename, always the same, "financials"
- sheet, always the same, "data"
- first critera range, will refer to whole column (no range names, no tables), "$M;$M" (="Active")
- second criteria range, will refer to whole column (no range names, no tables), "$C;$C" (=$A$1 on this sheet)

regards

David

 

[Eric W]

Sorry it's been a while. Assuming your formula looks like this:

=SUMPRODUCT('\\server\common folder\path\2024 R\[financials.xlsx]Data'!$A:$A,--('\\server\common folder\path\2024 R\[financials.xlsx]Data'!$M:$M="Active"),--('\\server\common folder\path\2024 R\[financials.xlsx]Data'!$C:$C=$A$1))


where A has the values you want to sum, then this macro will loop through all the folder names in BL13:BL16 and create that formula for each one.

Sub ClosedFormulas()
Dim c As Range, MyPath As String, w As String

    MyPath = "\\server\common folder\path\&&\[financials.xlsx]Data"
    
    For Each c In Range("BI13:BI17")
    
        If c <> "" Then
            w = "=SUMPRODUCT('" & MyPath & "'!A:A,--('" & _
                                  MyPath & "'!M:M=""Active""),--('" & _
                                  MyPath & "'!C:C=""" & Range("A1").Value & """))"
            c.Offset(, 3).Formula = Replace(w, "&&", c.Value)
'            c.Offset(, 3).Value = c.Offset(, 3).Value
        End If
        
    Next c
    
End Sub

You said these were Excel 2007, so you probably want to change the xlsx (in purple) to xls. The A:A in red is the column where the values are. You can leave the formulas if you want, and they'll auto-update, but if you just want the values, you can remove the comment symbol ' at the start of the line in green. That will replace the formula with the value.