External Links Not Updating when inside COUNTIFS

**sunsoar77** · 10-18-2012, 01:23 AM

Hi there,

I'm having trouble with building a master report for some trend reviews I've built.

I have 9 workbooks that are updated weekly, there is a small macro in them to clean up some formatting that is exported from my work's database. There is a tab in these work books that simply counts the number of times there is a value over a certain threshold. The threshold is variable and is pulled from a cell with data validation on it to force the threshhold to multiples of 100 up to 1000. It also has a variable date filter.

Formula looks something like this:

Please Login or Register  to view this content.

Finds matching entry in list, checks if the date falls between the two specified and then checks if it is over the threshold.

Problem I have is that I want to roll these up into a master trend report.

As soon as I alter the forumla to look like this:

Please Login or Register  to view this content.

It will update fine with both books open, but open the master by itself and i get a #VALUE error. I should note, the error occurs when I change my threshold drop down or date dropdown.

I've added the folder to my trusted locations, unchecked the 'Update Links to other workbooks' in the advanced settings, checked my source reference in the Links dialog (check source returns an 'OK')

Here's the kicker - I put a few single cell references so =West_Test.xlsm!Transactions'!A1 and they update fine. Make changes in one workbook, save, close, open master and it's updated to new value.

Any thoughts?

**nested.if1@gmail.com** · 10-18-2012, 02:04 AM

Can use Sumproduct instead of Countif.

**FDibbins** · 10-18-2012, 02:17 AM

some formulas/functions wont work on closed workbooks, countifs is 1 of them, sorry

**sunsoar77** · 10-18-2012, 02:21 AM

Originally Posted by nested.if1@gmail.com

Can use Sumproduct instead of Countif.

Not sure how sumproduct will do what I need to do? I'm only counting instances. There is no multiplication involved.

Can you explain how I could use sumproduct in lieu of countifs?

Originally Posted by FDibbins

some formulas/functions wont work on closed workbooks, countifs is 1 of them, sorry

Thanks for that. Are there any alternatives?

**FDibbins** · 10-18-2012, 02:24 AM

wishful thinking, but i dont suppose you could combine them all into 1 workbook?

**sunsoar77** · 10-18-2012, 02:39 AM

The roll up report is only 'needed' once per month. I think I'll just do a copy/paste 9 times each month to update the master review sheet.

Not an ideal solution but the best one right now. Maybe a macro in the future to handle all the copy/pasting with one button click.

I'll mark thread as solved as I've got an answer, just not the one I wanted!

Thanks for your assistance!

**nested.if1@gmail.com** · 10-18-2012, 03:29 AM

Try this...

=SUMPRODUCT((Transactions!F:F=A6)*(Transactions!C:C>=$H$3)*(Transactions!C:C<=$H$4)*(Transactions!J:J>=$F$2))

**sunsoar77** · 10-19-2012, 12:20 AM

Hi nested.if,

Formula worked OK for local purposes, but once I tried to use it with an external reference it failed.

Also took a long time to calculate data as the sheets I'm using are currently 500 rows and have between 20 and 50 rows added each week so that will only get worse.

Thanks for the suggestion, however I think I'll build a macro to do a copy paste for my purposes to keep the worksheet's responsive.