Sumproduct a variable external workbook based on cell value?

[Sumilidon]

Hello all,

I am hitting a bit of a wall trying to figure out this one and was hoping someone may be able to help.

In short, I have a large amount of data over a series of workbooks - all in the same folder, all in the same format. These represent each person's workload.

They all use a single, read only file to update their entries and this is done via a macro. They select their name in Cell B1, and the Macro uses this name to open the correct file when they press the update button. This is all working fine.

The problem is, they sometimes need to stop on a piece of work and return to it a few days later. Normally this is not an issue, I would use a sumproduct formula to total all the previous time spent on the task where the task ID matches. unfortunately I only know how to do this against a specific, fixed database when what I am after is for the formula to look in workbook with the name that is in cell B1 - just as with the macro to update the file.

Does anyone have any ideas how I can do this? The formula would need to match 2 criteria (Case Type and Case ID) and then sum a specific column. Lets assume Column A is the Type, B is the ID and C contains the times to be summed together.

Any help much appreciated. This is the final piece in an otherwise massive project that I simply cannot get my head around.

[brant.wheeler]

I know this may be improper to say, but I do not think this is possible using normal Excel formulas. You can probably get really fancy and pull this off, but why? The INDIRECT() formula would normally be the way to go for something like this, but it only works for external references if the externally referenced workbook is opened. There are 2 simple solutions I see here:

1) Pre-link to all of these cells in a hidden or veryhidden tab to create a vlookup list that can be used when the name is changed.
2) Program it in VBA

I personally prefer the second approach and can code it for you if needed. Since it sounds like other things are running on macros, I believe this would be a good option.

Thanks!