VBA Macro for SUMIFS formula linking different excel file

[yyexc]

---

Hi there, is there any way to write a VBA macro sumifs with criteria linking other Excel file, start from column H row 12 onwards

 

[vletm]

yyexc​

Your is there any way to ...
Answer is Yes,
but without other Excel file, there will be challenges to test and verify - how well do it work?
... You should able to send a sample Excel-file, which has some sample data as in real Excel-file.

 

[yyexc]

Thanks I uploaded the sample excel file
Direct is the main file I need to have a vba macro on it to link with 2 others excel files with sumifs formula shown

 

[yyexc]

So far I could only manage to update 1 of the other linked excel files as the file size is too large

 

[vletm]

a sample Excel-file
and You can save that file as .xlsb too ... then it'll be many times smaller too.

 

[yyexc]

Thanks, I've reupdate my files as below and saved in xlsb version for sample excel files

 

[vletm]

yyexc​

This sample works basically same way as Your direct.xlsx.
You could compare and modify it almost as with Your formulas.
Instructions:
#1 Have below file and above two data-files in same folder.
#2 Open direct.xlsb-file
#3 Press [ Do It ]-button
#4 Check results

 

[yyexc]

Thanks, really appreciate ur time on the VBA coding, may I ask how about if the 2 refer excel files is in the different folder?

 

[vletm]

... and next ... if data-files names are different?
You can choose those two valid files by selecting right side of wbN cell.
Note: I cannot test this, if You're using Windows-version.

 

[yyexc]

Thanks vletm, really appreciate ur time on it.
I have some concern, 1.Do we have another ways so that the reader can get to know the information of the formula without looking into the macro coding?
2. Also since the reference columns are hard coded into macro coding, is it possible to use a match formula instead, so that the macro will automatically know which column to extract the info from, even if there is new columns added in the linking other Excel files, the consideration of hardcoding the columns in the macro coding would have to manually update the coding for all columns if any update is needed.
**I am thinking of an idea of using index match function to link with other excels file (Jan, Dec), so if these other excels file having any added column, it will not affecting the formula.
The index match function will apply on first row of the direct file only for the extraction, and then can we use macro after this, which we copy the formula on the first row till the end and paste it as value only from 2nd row onwards (but we maintain the formula show out in first row).
How do you think on it? And how can the macro adjust for this?

 

[vletm]

#1 Why should the reader look coding?
#2 Did You write with criteria linking other Excel file, start from column H row 12 onwards?
Me or Excel won't guess or expect something else.
Did You give any hints - what should match or something?
Do direct.xlsb or Your original file have any other hints that someone would add or delete or mess something?
If You think to use some formulas Yourself then okay .. I won't think to use, I would coding.
But without clear details, I cannot modify anything.