CODEWITHSUNDEEP
excelexcel-formula
Sam
Sam

Reputation: 43

Formulas not updating if the other work book is closed

I have the below formula which makes reference to another workbook in the same folder but when the file is open it just works fine, the problem is when the other excel file is closed because the formula wont get updated

Formula when the other workbook is open

=IFERROR(COUNTIFS(Auto_Zero.xlsx!MonthDB,B6,Auto_Zero.xlsx!CSRDB,C2),"")

Formula when the other file is closed

=IFERROR(COUNTIFS('C:\Users\csamayoa\Desktop\QA 
Test\Auto_Zero.xlsx'!MonthDB,B6,'C:\Users\csamayoa\Desktop\QA 
Test\Auto_Zero.xlsx'!CSRDB,C2),"")

I have tried a lot of different suggestions and the formula does not wok when the other file is closed :(

Upvotes: 1

Views: 7997

Answers (3)

MacroMarc
MacroMarc

Reputation: 3324

So an simple formula would be something like:

=SUM((Auto_Zero.xlsx!MonthDB=B6)*(Auto_Zero.xlsx!CSRDB=C2))

Use Ctrl-Shift-Enter to enter the formula. Curly brackets should appear magically in the formula bar when this key-combination is used.

Upvotes: 0

EEM
EEM

Reputation: 6659

Replacement Formulas for Formulas Linked to Closed Workbooks:

Formulas such as COUNTIF, SUMIF, COUNTIFS and SUMIFSwhen linked to closed workbooks do not update their results.

The list below presents the replacement formulas that work with links to closed workbooks. It shows the syntax of the actual formula and its replacement in order to ease translation.

=COUNTIF( range, criteria )
=SUM(( range = criteria ) * 1 ) '*Entered as Formula Array*

=SUMIF( range, criteria, sum_range )
=SUMPRODUCT(( range = criteria ) * 1, sum_range )   

=COUNTIFS( criteria_range1, criteria1, [criteria_range2], [criteria2], ...)
=SUM(( criteria_range1 = criteria1 ) * ( [criteria_range2] = [criteria2] ), ...) '*Entered as Formula Array*

=SUMIFS( sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)
=SUMPRODUCT( sum_range, (criteria_range1 = criteria1 ) * ([criteria_range2] = [criteria2]) * ...)

Formulas Array are entered pressing [Ctrl] + [Shift] + [Enter] simultaneously, you shall see { and } around the formula if entered correctly

Upvotes: 2

Kyle
Kyle

Reputation: 36

Excel Functions like COUNTIFS and SUMIFS does not recalculate when referenced to closed workbook. You could try using Excel Query Designer which work like ADO codes. Allows retrieval from closed books, db etc. Hope this helps.

the simplest way to achieve this without using advanced tools will be to use the code below to open the file, do the calculation and close the file back. Not sure if this help. Please change the 'H:\My Documents\4674576.xlsx' to your source file path. Paste this code in new module of your excel workbook. run the code and see if this helps.

Sub loadfileandCalc() Dim acWb As Workbook Dim wb As Workbook Set wb = Workbooks.Open(Filename:="H:\My Documents\4674576.xlsx", UpdateLinks:=False, ReadOnly:=True) Set acWb = ActiveWorkbook ActiveSheet.Calculate Set acWb = Nothing wb.Close False Set wb = Nothing

End Sub

Upvotes: 2

Related Questions