Using Indirect in a SUMIF calculation

Question

I hope that someone will be able to help me solve a small problem I am trying to overcome.  I have W7 with Excel 2010 and the files are stored on a server. 

I have a excel file containing information for the year in month workbooks, i.e. January, February, etc. 

There is a 'Summary' workbook that has the monthly figures for the whole year. 

Currently the calculation used to get the data, for January, is:  =SUMIF(January!$A$3:$A$104,A9,January!$C$3:$C$104)

The calculation is copied to the next cell and 'January' is changed to 'February'.  The same is done for all 12 months. 

The 'Summary' workbook also includes previous years data in the same format as above. 

The calculation is very similar except it gets the data from a particular cell. 

='\CompanyName\Company\Folder\Folder2\Folder3[FileName - January 2013 - December 2013.xlsx]Summary'!$B$5

I would like to change the calculation to use a variable for the filename used i.e. change the address of where the file is and the Filename. 

Can anyone help me to change the calculations. 

Thanks for your help in advance. 

Regards

Dave

Answer 1

While I'm sure that you are working with a set of worksheets (internal and/or external) that are actually open in the current workspace, it may be informative to others reading this thread to note that using INDIRECT() to concatenate external references cannot be used to reference closed workbooks.

From the INDIRECT function's Office.com reference page:

• If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

Hyperlink	Description
SUMIF function	Adds the cells specified by a given criteria
SUMIFS function	Adds the cells in a range that meet multiple criteria
INDIRECT function	Returns a reference indicated by a text value

FWIW, your formula (see below) works fine for me.

=SUMIF(INDIRECT("'"& $C$7 & "'!$A$3:$A$100"),A9,INDIRECT("'"& $C$7 & "'!$C$3:$C$100"))

Answer 2

I am sorry but I didn't explain myself well. 

To get the previous data the previous file must be open when the data is requested.  Sorry. 

I have worked out how to get the data from the current workbook which contains the data for the current year: 

Where B7 = the workbook name to be used e.g. January, February, etc. 

and A9 is the data type used in the calculation, e.g. Agency, Prof, etc.

=SUMIF(INDIRECT("'"& $B$7 & "'!$A$3:$A$100"),A9,INDIRECT("'"& $B$7 & "'!$C$3:$C$100"))

What I need help for is in creating the calculation to get data from the workbook in last year's file, e.g.: 

=SUMIF('[Commercial - January 2013 - December 2013.xlsx]January'!$A$3:$A$100,A20,'[Commercial - January 2013 - December 2013.xlsx]January'!$C$3:$C$100)

What I want is the be able to build the name of last year's file, using a variable for the year and month and then go to the file / workbook to get the relevant months data. 

What I would like to do is build the filename and then use the same command as for the current year: 

Where, say C7 = the filename and workbook name of the data being requested: 

Commercial - January 2013 - December 2013.xlsx

Then build the month required: e.g. January, February, etc. 

So I have in cell C7:  '[Commercial - January 2013 - December 2013.xlsx]January'

Then the calculation in the cell will look like: 

=SUMIF(INDIRECT("'"& $C$7 & "'!$A$3:$A$100"),A9,INDIRECT("'"& $C$7 & "'!$C$3:$C$100"))

I hope I have now explained myself correctly and fully.  Sorry for the confusion. 

I have been trying different things but I have been unable to get it to work.  Any help would be appreciated.

Answer 3

Hi,

INDIRECT doesn't work on a closed file.

You might interested to read a article on the same here.

http://www.contextures.com/xlFunctions05.html

Answer 4

Thank you for your reply Ashish. 

I am not worried about getting the previous data other than when the workbook is created, which is when the previous files will be open. 

It is using the variable calculation that is the important bit of my request. 

What I didn't explain originally is that I will build the Year from the filename so that when a new file is created for the new year then the formula will still work and the history and the year is built automatically. 

This is not relevant to my question really as I have already done this I just need help on changing the calculations to use the INDIRECT command so that the year and Month is created automatically and changes when required. 

Thanks again for your help and comments.

Answer 5

Hi,

Please note that the INDIRECT function will work only as long as the other workbook is open.  If that is not the case i.e. if you wish to extract data from a closed workbook, then you may refer to my solution at the following link - http://www.ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/

Hope this helps.