USING COUNTIF to reference closed workbooks

[ItzikM]

Hi All!

I wish to dynamically upate a target workbook with references to a large number of source workbooks without having all the source workbooks open on my local PC. I have people on the phones working on anything up to 25 separate workbooks. I have created a target spreadsheet that functions as a dashboard and allows me to monitor “live” any changes made in any these workbooks. Most of my formulae are “COUNTIF” style formulae using “INDIRECT” and they work because all the source workbooks are shared and open on my local PC. In order to refresh the counts on my dashboard I have a macro that saves all open workbooks. How can I dynamically reference my source workbooks without them all being open on my local PC? Please note that some of the source workbooks might be closed but the majority will be open by other users.

Thanks in advance.

Regards

Itzik

[Glenn Kennedy]

You can use SUMPRODUCT.

This style for COUNTIF:
=SUMPRODUCT(--('C:\Users\Windows 8\Desktop\[Book3.xlsx]Sheet1'!$A$2:$A$10 =$A2))
or this for SUMIF:
=SUMPRODUCT(--('C:\Users\Windows 8\Desktop\[Book3.xlsx]Sheet1'!$A$2:$A$7=Sheet1!A2),'C:\Users\Windows 8\Desktop\[Book3.xlsx]Sheet1'!$B$2:$B$7)

[ItzikM]

Thanks so much Glenn. I will play with this and let you know how I go. Regards, Itzik

[ItzikM]

Hi Glenn!

Thanks. I have had some success so far with converting my simple "COUNTIF" formulae using your first formula. However I cannot figure out how to do the more complex countifs with sumproduct.

If you could imagine a table where in column A you have ID numbers that describe regions. Numbers 1000000 to 1999999 are one region, 1200000 to 129999 are another etc. In Columns L and M you have call results - first call attempt in column L and second attempt in collumn M. Values in the results columns could be "NC" for no-contact, "G" for "Get" etc. When I wish to know how many calls in region #1 (<1200000) produced results either "G" or "SC" I have been using the following formula:

=SUMPRODUCT(COUNTIFS(INDIRECT("'[Rejects "&$B$2&".xlsx]Metro'!$A:$A"),">1000000",INDIRECT("'[Rejects "&$B$2&".xlsx]Metro'!$A:$A"),"<1200000",INDIRECT("'[Rejects "&$B$2&".xlsx]Metro'!$L:$L"),{"g","sc"}))+SUMPRODUCT(COUNTIFS(INDIRECT("'[Rejects "&$B$2&".xlsx]Metro'!$A:$A"),">1000000",INDIRECT("'[Rejects "&$B$2&".xlsx]Metro'!$A:$A"),"<1200000",INDIRECT("'[Rejects "&$B$2&".xlsx]Metro'!$M:$M"),{"g","sc"})

How can I adapt this so that it will work when the workbook is close?

Thanks again for your willingness to help.
Regards
Itzik

[Glenn Kennedy]

Is this the formula in its unaltered state, or one you've been trying to adjust?

[FDibbins]

A word of caution - try and avoid using full-column ranges with SP, it will start to slow your file down, rather, use specific ranges.

[Tony Valko]

It's not the SUMPRODUCT function that's referencing the entire columns, it's the COUNTIFS function.

The COUNTIFS function can do this more efficiently.

[Glenn Kennedy]

Sorry, I must have been sleeping when I first looked at this. INDIRECT does NOT work on closed sheets. There is a free add-on (INDIRECT.EXT) that does work in currrent versions of Excel; but it MUST be installed on all PCs that will want to access closed files. By comparison with that - the use of SP on whole columns is of minor concern!!!

[Glenn Kennedy]

However, all is not/may not be all lost yet. Some years back, Harlan Grove wrote a UDF called PULL, which does the same thing as INDIRECT, but on closed sheets. I have modelled your set up and it seems to work OK. However my knowledge of VBA is woeful (at best) and I have no idea how to make the function auto-update!! CTRL-ALT-SHIFT-F9 will make it update. The formula is quite horrendous:

Please Login or Register  to view this content.

but it works. See the formula in Book3 (macro-enabled) and the source data in Rejects12.

[ItzikM]

Hi All!

Firstly, Glenn, that was the original code before I tried (unsuccessfully) to modify it using Sumproduct. I posted it so you could see how I have been using countifs to manage the conditions and to show you the most common kind of task that I need my code to perform. If there is a way to achieve the same goal with closed workbooks using some other function such as sumproduct that would be ideal. But thanks so much for showing me how I could use Glen's "Pull" function for my formulae. I will study your examples, play with it a bit and see how I go.

To all the others who have responded, thank you. Sadly, I am aware that INDIRECT does not work for closed workbooks. I have seen references to Harlan Groves' code (and to others who have also written massive vb code for INDIRECT) but figuring out the formulae for those codes is above my skill level at the moment.

Again, thanks so much to all those who have invested time in my small problem.

Regards

[Glenn Kennedy]

OK. let me know how it works out. I'll be a bit "thin on the ground" for a few days, but will look in from time to time.