INDIRECT function

**rageagainstworkbooks** · 12-21-2011, 12:26 PM

Hi

This is my first post and today has been a very frustrating day trying to get excel to do what I want. I feel the problem lies with my poor skills rather than excel, however here is what I want to achieve.

The scenario

There are 18 offices. Each office has a monthly sheet of data. Each month I want to create a self-populating sheet which records figures from each of the 18 monthly sheets.

The files are named the same each month except the for a small piece of the file name changing. Eg "June 2011 Statistics Recording Sheet - East" will be "July 2011 Statistics Recording Sheet - East" the following month.

I want to have a function that lets me change the month and year in the book name so that this updates the entire monthly total sheet.

At present I have to update the function manually in every cell on that sheet to show the correct month in the filename.

To complicate matters the files are all stored on sharepoint.

to give you an example of one of my formulas:
=SUM('http://sharepoint.scotland.net/sites/RegistrationService/Stats/Shared Documents/East/[June 2011 Statistical Return Sheet - East.xls]Sheet1'!$E$35)

Basically, how can I get this formula to make reference to another cell to get the month and year part of the filename. This will allow me to change the value in this other cell once a month and the whole total sheet will populate with the correct figures.

I have been trying all day, I am already bald so have had no hair to pull out.

Sorry if the description of my problem is not well worded.

Hope somebody has an idea.

**NBVC** · 12-21-2011, 12:41 PM

This can be done with INDIRECT, but unfortunately, this function does not allow the source workbook(s) to be closed... so you will need to have them open when using this sheet.

Alternatively, there is a free addin available that will allow you to use reference closed workbooks, if that is of interest.

If yes, then please also elaborate which cell(s) contain the date to use in the formula and how are those dates entered into those cells (i.e. are they real dates or just text?).

**rageagainstworkbooks** · 12-21-2011, 07:57 PM

Hi, thanks for the reply.

The date cell would just be on sheet with the totals, i.e. the same sheet that the function wll be on, in any random cell, say X6 for example.

Its currenly formatted as text. June 2011 or July 2011 etc.

**NBVC** · 12-21-2011, 11:19 PM

So if you are going to have the source book open then:

=SUM(INDIRECT("'http://sharepoint.scotland.net/sites/RegistrationService/Stats/Shared Documents/East/["&X6&" Statistical Return Sheet - East.xls]Sheet1'!$E$35"))

If not, then download the free addin called Morefunc.xll from here and try:

=SUM(INDIRECT.EXT("'http://sharepoint.scotland.net/sites/RegistrationService/Stats/Shared Documents/East/["&X6&" Statistical Return Sheet - East.xls]Sheet1'!$E$35"))