Expanding a range across multiple sheets

Hola!

I'm in need of a little pointer/advice.

The following formula extracts invoice data for use as part of a 'statement of account' workbook, with separate sheets for each month.

=IF(ISERROR(INDEX('[Sales 2013-2014.xlsx]April 2013'!$B$3:$K$50,SMALL(IF('[Sales 2013-2014.xlsx]April 2013'!$C$1:$C$50=$A$8,ROW('[Sales 2013-2014.xlsx]April 2013'!$C$1:$C$50)),ROW(2:2)),5)),"",INDEX('[Sales 2013-2014.xlsx]April 2013'!$B$1:$K$50,SMALL(IF('[Sales 2013-2014.xlsx]April 2013'!$C$1:$C$50=$A$8,ROW('[Sales 2013-2014.xlsx]April 2013'!$C$1:$C$50)),ROW(2:2)),5))

It's working beautifully for 1 month (April 2013), but I would like to modify the above so that it uses the same range for all 12; 'April 2013', 'May 2013', 'June 2013', 'July 2013' etc etc.

Any feedback gratefully received.

Can you post a workbook with representative data illustrating the problem? The formula is only showing a small "picture" of what you have and what needs to be done.

Both workbooks that you have uploaded are Sales 2013-2014. There isn't a Statement of Account that I can find.

Oops!

What an idiot, I pasted the same URL twice. Here's the Statement of Account file:

https://www.dropbox.com/s/5lhydmkswo...%20design.xlsx

This is turning into a nightmare unless I can find a way to address both the name and the workbook tab within a single formula which I have not been able to do without creating a situation that cannot be maintained by you.

I'm researching the addressing of external references but so far have only found references to single variables and not two like you require. The solution my be in the use of VBA or a different arrangement of the sales data to facilitate the retrieval of data.

Is there a practical reason why the data has 1 worksheet per month? This seems to be very inefficient on a number of fronts.

Please delete................duplicated post

I could only get this to work as a single workbook. The links to the workbook via cell reference just didn't get along with the cell reference for the worksheet.

Sorry NDM, I've been out of the office for over a day and am just catching up. I'm on my home PC at the moment and don't have Excel on it, but I'll have a play with that when I'm back tomorrow.

I do want you to know though that I really appreciate you having taken the time out to help me as you've done.

Just to quickly answer your earlier question, unfortunately the Sales sheet is kind of set in stone (I would rather it was all in one sheet, and makes more sense to me) unfortunately the "powers that be" insist that is the way it must be (I suspect it's purely because that's the case as it way it was originally set up rather than it is the best solution).

I had a notion of making an intermediary worksheet that would harvest the Sales sheet data onto one page and that to be used for the SOA.

Anyway, thanks ever so much for what you did and I'll report my findings tomorrow!

If that is the case, it is sad because you can get all the reports that each monthly sheet has by filtering a single data set and have far more control. I think some "education" is in order.

I can't get the double cell reference to work in addressing worksheets in an external data source. I can't even find any references to doing so. I'm afraid that I am at the end of what I can accomplish unless I get hit by "lightening" or something. Perhaps someone else has an insight into how to accomplish this.

I think that this is what you are intending. Seeing that you have tabs for each division, there is no longer a need for the Data Validation on each tab. I eliminated that DV in B2 of each worksheet. As data is added to the download worksheet, each tab is automatically updated.