inserting variable filename into sumif formula

**sirensiren** · 01-07-2010, 05:57 PM

Hello, might be a simple problem, but i don't have the knowledge to figure it out. Basically i have a formula that collects data from an outside worksheet. This formula is repeated several times on the sheet, each calling to a different workbook.

Problem, it's a pain to have to type in each file name each time, with only two our three characters changing for each one. Is there a simple way to have it automatically fill out the file name based of the cell adjacent? (in this instance its all in reference to dates.

Here is the function i have:

Please Login or Register  to view this content.

The only things that change will be the date on the file name (..."12.18.09, 12.19.09, etc"...) and then some times the month (i do two weeks of information at a time)

Does that make sense? Thanks - Luke

**darkyam** · 01-07-2010, 06:03 PM

You can put the date into a cell (might have to format the cell as text first) and then use the Indirect function: =SUM(Indirect("'C:\doc...\"&A1&"\["&A2&".xls]... where A1 has the month and A2 has the date.

**sirensiren** · 01-07-2010, 06:14 PM

Its having a problem with the end of it.

"sum(indirect('"C:\Doc....\["&D3&".xls]Sheet1'!C5+C6"')"

That C6"' keeps being highlight and said its wrong. if i take it away it just says #ref!

so i can't tell if that works or not.

**darkyam** · 01-07-2010, 06:23 PM

I don't think there should be a ' after C6.

**sirensiren** · 01-08-2010, 12:18 AM

I've tried with every combination with ' or " or nothing. The only thing that doesn't give me an error is with nothing, but it still gives me #ref!

**darkyam** · 01-08-2010, 01:53 AM

Perhaps C5:C6 instead of C5+C6.

**DonkeyOte** · 01-08-2010, 05:20 AM

If the target files are not open as and when calculations are performed (implied by link) this approach will I'm afraid to say fail for two reasons

1 - SUMIF can't be used on closed files

2 - INDIRECT can't be used on closed files

Problem 1 can be circumvented using SUMPRODUCT as opposed to SUMIF however Error 2 is fatal.
If you need to use variable closed files in your formulae you're looking at VBA or 3rd party tools like Laurent Longre's morefunc.xll - specifically the INDIRECT.EXT function contained within.

If all of the target files are always open as and when these calculations take place then the INDIRECT with SUMIF approach will work (with correct syntax) however being Volatile they will generate (undesired) calculation overheads.

You might want to have an overall re-think of your approach and rather than using "variable" formulae simply use Static Links and subsequently use Edit -> Replace to update the file references as and when required ?