Reference Cell or Date within formula workbook link

[GeorgeMustang]

Hi,

Not sure if this can be achieved via formula alone or whether VBA is required. However what I am looking to do is link within a formula that references a workbook which contains a date.

To add some more context, I get a new excel file daily produced from a Access database via SAS, these files contain daily volume figures that I want to reference into a capacity worksheet to measure capacity vs volume.

Note that these files I am wanting to plot in advance before they even exist for the the entire year.

The file format will be something like this 'Volumes (01JAN15).xlsx' and the to make it further complicated the day is always 1 day negative so on my capacity I would be referencing in column that is for 02/02/15 but wanting to extract data from the file that contains a date from the previous day.

Ok so if i put this into an actual formula - not necessairly the one at this stage I will use as SUMIF is not great for linking to closed workbooks but just for example at this stage.

=SUMIF('[Volumes (01JAN15).xlsx]North_Region'!$A$2:$A$16,"Volumes",'[Volumes (01JAN15).xlsx]North_Region'!$C$2:$C$16)

I have tried putting the dates in excel cells above the columns that my formula is contained in and referring it is as a cell so something like

=SUMIF("'[Volumes"&"(A3)"&".xlsx]North_Region'"!$A$2:$A$16,"Volumes","'[Volumes"&"(A3)"&".xlsx]North_Region'"!$C$2:$C$16)

Where (A3) = (01JAN15)

Which unfortunately did not work. I am also wondering if I will need to use something like (IF(ISERROR(my formula)),"-",(my formula)) as the files will not exist until that date occurs.

Any help would be greatly appreciated as this could save me considerable time and also I think it raises an interesting topic which I have not been able to find any posts on the web anywhere about.

[FDibbins]

If you want to link to another workbook, using a cell ref containing that WB name, you need to use INDIRECT(). However, INDIRECT() only works on open WB's. Perhaps take a look at the Morefunc add-on for that?

Not sure why you say SUMIF is not great for linking to closed WB's, it works just fone for that

[GeorgeMustang]

Thanks for the quick reply Ford, I will look into the MoreFunc addon you mention. When i've used SUMIF to link to a closed workbook it works fine when the workbook is initially open but as soon as it closes i get #N/A appear as a result. I did some searching around and there appeared to be a general consensus of SUMIF not working with closed workbooks and to use SUMPRODUCT instead. Have I missed something here?

Thanks

[FDibbins]

Sorry, I was mistaken, SUMIF() also only works on open WB's, I guess I have always had the sourse file open

[GeorgeMustang]

Thought I wasn't going mad, I'll post back if I come up with a solution...