SumIf from various files

[VegasNath]

I have attached a mock-up version of a sumif task that I need to complete from 48 different files. The files have different names and are in different locations. The files are password protected to gain entry. All of the data is laid out in the same fashion, but I need to bring it all together in a summary.

My guess is that I will need to open each of the files in order to generate the data, but how is the best way to change each column based on a different source file?

I would appreciate any advice that can be offered by way of the best approach to such a task.

[HansV]

If you'd try to do this with links, the whole thing would probably become impossibly slow. I'd write code to open each of the files in turn, get the values you need, and write them to the appropriate cells. You'd have to re-run the code if you need to update the values.

Alternatively, import the 48 sheets into one workbook.

[VegasNath]

If you'd try to do this with links, the whole thing would probably become impossibly slow.

Agreed. I think that I will need to use formula's / links, but convert the links to values as I go.

I'd write code to open each of the files in turn, get the values you need, and write them to the appropriate cells. You'd have to re-run the code if you need to update the values.

Probably the best option, but I'm not sure where to start with that.

Alternatively, import the 48 sheets into one workbook.

I fear the size would be massive.

[HansV]

Perhaps a database would be better suited to your purpose.

[VegasNath]

I managed to do this by creating the entire setup based on one file and then using Edit | Links | Change Source, and converting the formulas to values as I went through the various files. Not ideal, but not as bad as I originally thought.

Thanks