Excel SUMIFS formula with reference to other workbook

0
votes

Im trying to write a VBA code for which will fill my excel SUMIFS formulas. In general SUMIFS will calculate sums based on other workbook data. Other workbook filename is currently hardcoded in my macro, however i'd like to make it more flexible and instead of hard coding it i'd like to use a cell in which i put the file name.

so far macro creates the following formula and puts it in cell:

=SUMIFS([SW.xlsx]TeamAllocations!F$9:F$401,[SW.xlsx]TeamAllocations!$D$9:$D$401,$D16,[SW.xlsx]TeamAllocations!$B$9:$B$401,$B16)

what i'd like to have is kind of:

=SUMIFS([<variable evaluated based on a cell which has filename/filepath, eg B1>]TeamAllocations!F$9:F$401,[<variable evaluated based on a cell which has filename/filepath, eg B1]TeamAllocations!$D$9:$D$401,$D16,[<variable evaluated based on a cell which has filename/filepath, eg B1]TeamAllocations!$B$9:$B$401,$B16)

So in B1 cell i'd have "SW_01.xlsx" or "SW_02.xlsx", so the SUMIFS formula will always have up-to-date soruce. Is that possible in excel?

Thanks J.

1
excelvbaexcel-formulasumifs
You can do this by replacing [SW.xlsx] with ` & varRng.Value & `. – shrivallabha.redij
okay, maybe i need to repharse question: how to have it varRng.Value not in macro but in the end formula? normally instead of [SW.xlsx] i would put [='settingsSheet'C1] where in C1 cell i have path to my file? – JosiP

1 Answers

0
votes

If all your files have a fixed format, I think you can combine all of the files with power query. You can do what you want more easily.

Combine Files:

https://chandoo.org/wp/combine-excel-files-using-power-query/

Group in PowerQuery (same as SUMIF):

https://support.office.com/en-us/article/group-rows-in-a-table-power-query-e1b9e916-6fcc-40bf-a6e8-ef928240adf1

Useful filter:

https://exceleratorbi.com.au/pass-excel-parameter-power-query/