Sumifs on Multiple workbooks

[derrickhnj]

I have a master spreadsheet where we keep all our tax adjustments (M-1's) that I am looking to automate using the sumifs formula.

Basically, we have 120 separate M-1 files on our local drive with summary tabs and I'm saying if this summary sheet has a certain tax adj. code and for a certain entity, sum this column. So I need the sumifs for multiple queries (Entity and Tax Adj. code) for the Master Spreadsheet. Problem is Sumifs gives a #Value when these 120 workbooks are closed.

Is there any way to work around this? I tried an internet search and saw something on the MSFT site about using Array's, tried it but gives the same #Value problem and it also fails to lookup the information on the left columns in my master sheet.

Please help me, my spreadsheet is awesome and automated if only sumifs worked on closed workbooks.

Thanks,
Derrick

 

[DoubleD]

I think you are going to be hosed if you want to use SUMIF. But, if you switched all of your formulas to =SUM((....)) and made this an array formula it would work on closed workbooks. Depending on how many formulas you have this may not be a trivial update. Another option would be to have a simple macro that identifies all of the workbooks that you are linking to and opens them, recalculates and closes them without saving. This would give you fresh values and could be automated pretty easily. At least you have options.

 

[PKTEE]

Sumif do not work extracting information from different workbook.

Use sumproduct to resolve your problem

 

[derrickhnj]

I think I have to try the Macro to open all workbooks approach. Arrays simply don't work for my application.

This is odd though, once I made my workbook macro enabled (extension is now .xlsm) the sumifs didn't go to #value, and even when I close the file then re-open it the #value's don't come up anymore, does this sound right that just by making my workbook macro enabled Sumifs will work on closed workbooks?

Thanks,
Derrick

 

[PKTEE]

It shouldn't work.

the #value didn't show up because you didn't click the option button
on the security warning message as the link had been disabled.

 

[derrickhnj]

I wrote the macro to open and close all the workbooks, worked out to 94 workbooks when I was done. It works great, only thing is you have to switch to manual calculations once you run the macro so that the links don't try to update when you filter data.

I have one question though, some of the workbooks I don't have to click the "do you want to save before closing" (no) button, and some I have to click it while the macro is running. Is there anyway to write into my macro so that I don't have to do any clicks while the macro runs?

Here is what my code looks like, and this is basically repeated for 94 different path files:

Workbooks.Open Filename:= _
"J:\COM_TAX\FEDERAL INCOME TAX\1120\1_30_10\SchM-1\Dividend Elimination Entry.xlsx" _
, UpdateLinks:=0
ActiveWindow.Close

If you didn't have to click the No don't save before closing button this macro would be perfect and would probably run in less than 1 minute.

Thanks for the help guys.
Derrick

 

[Salamander]

If you didn't have to click the No don't save before closing button this macro would be perfect and would probably run in less than 1 minute.

Hi Guys,

This will close without asking if you want to save changes:

ActiveWorkbook.Close savechanges:=False

S.

 

[derrickhnj]

Hi Guys,

This will close without asking if you want to save changes:

ActiveWorkbook.Close savechanges:=False

S.

AWESOME!! That does it, now the macro runs in less than 2 minutes (3 files are very large that cause the problem) but 2 minutes to update 94 files in one worksheet for our department to see saves so much time on the backend.

Thanks so much for your help.

 

[derrickhnj]

Would you guys know why the values for 5 of my M-1's (Files opened) are not coming in even though the Macro is the same for these 5 files. I thought maybe it was a memory lag, but it's these 5 everytime.

here is my formula I have for one of the 5 that is giving me a problem:

=SUMIFS('J:\COM_TAX\FEDERAL INCOME TAX\1120\1_30_10\SchM-1\[P990502 Penalties.xlsx]M-1 Upload'!$K:$K,'J:\COM_TAX\FEDERAL INCOME TAX\1120\1_30_10\SchM-1\[P990502 Penalties.xlsx]M-1 Upload'!$A:$A,$B1093,'J:\COM_TAX\FEDERAL INCOME TAX\1120\1_30_10\SchM-1\[P990502 Penalties.xlsx]M-1 Upload'!$C:$C,$D1093)

Here is the formula for one that isn't giving me a problem:

=SUMIFS('J:\COM_TAX\FEDERAL INCOME TAX\1120\1_30_10\SchM-1\[Dividend Elimination Entry.xlsx]M-1 Upload'!$K:$K,'J:\COM_TAX\FEDERAL INCOME TAX\1120\1_30_10\SchM-1\[Dividend Elimination Entry.xlsx]M-1 Upload'!$A:$A,$B11,'J:\COM_TAX\FEDERAL INCOME TAX\1120\1_30_10\SchM-1\[Dividend Elimination Entry.xlsx]M-1 Upload'!$C:$C,$D11)

They are identical so I don't know why they aren't showing up once I run my macro that opens up the workbooks (and I do see them open and close).

Thanks if you have any idea.
Derrick