Complex Excel COUNTA query using INDIRECT to point to a sheet in a different workbook

0
votes

I'm trying to make Column E in Workbook A display the number of reports a manager has submitted in a given month in Workbook B. I want to use the COUNTA formula to count the number of non-blank cells in Column A of Workbook B, which would be equivalent to the number of submitted reports. However, there are multiple managers and each manager has their own sheet in Workbook B. For example, manager John Smith logs submitted reports in Workbook B on a sheet called "Smith". The name of the sheet in Workbook B is located in cell Q3 of Workbook A. I tried to make this work with the following code:

=COUNTA((('[Workbook B.xlsx]' & 'INDIRECT(Q3,FALSE)' & '!$A$2')),('[Workbook B.xlsx]' & 'INDIRECT(Q3,FALSE)' & '!$A$500))

Ideally, this code would count the nonblank cells from A2:A500 in Workbook B and display the number in Workbook A.

2
excelvba

2 Answers

0
votes

I managed to get this work by slightly changing your code. Indirect(Q3) was giving me a #ref error, so I just made it Q3 by itself with another indirect after the COUNTA and it worked. 

=COUNTA(INDIRECT("'"&"[Workbook B.xlsx]"&Q3&"'!$A$1:$A$500"))
0
votes

Tried my hand at making a macro for this. It might not be perfect, but hopefully it will give you a good starting point.

Sub count()
Dim cnt As Integer
Dim rng As Integer
Dim index As Integer
Dim shtName As String

shtName = Range("Q3").Value

Workbooks.Open ("C:\Path\to\WorkbookB.xlsx")

ActiveWorkbook.Sheets(shtName).Select

rng = ActiveSheet.UsedRange.Rows.count

For index = 1 To rng
    If Range("A" & rng).Text <> "" Then
        cnt = cnt + 1
    End If
Next

ActiveWorkbook.Close False

ActiveWorkbook.Sheets("Sheet1").Activate

Range("E1").Value = cnt
End Sub