Microsoft Office Forums - View Single Post - #VALUE! with COUNTIFS that reference other workbooks

Rapidos · 02-12-2019, 04:38 AM

I created a worksheet with cells that take value from other workbooks, like this:

Code:

=COUNTIFS('adress\[name.xlsm]TAT'!D:D,"="&A6,'adress\[name.xlsm]TAT'!B:B, "=Timed out Cases")

And I have created a macro for automatically updating the values upon opening the workbook that looks like this:

Code:

Private Sub Workbook_Open()
    Workbooks.Open "name.xlsm", UpdateLinks:=1, ReadOnly:=True
    ActiveWorkbook.Close SaveChanges:=False
End Sub

Yet somehow only half of my values get updated upon opening (I have 12-15 columns for each workbook I access):

wb1 wb2
#VALUE! 3
#VALUE! 5
#VALUE! 9
#VALUE! 0
#VALUE! 1

So I created a separate macro for opening all the workbooks again when I have already opened my report. And what happens now is that the previously updated columns get #value! and the non-updated half updates:

wb1 wb2
4 #VALUE!
4 #VALUE!
5 #VALUE!
6 #VALUE!
7 #VALUE!

Can anybody help me figure out what is going on here? Thanks.