I have a very tough one here that I am struggling to solve. The back-story is:

Every month I receive a fresh report of 20k+ rows and over 50 columns. It is all contained on one worksheet. Each month, I add the most recent report to a new worksheet in a master file - the worksheets are named after the relevant month.

The foremost sheet on my master file is the dashboard, which uses data validation drop-down lists to pull through the correct data from the other sheets (named after the months):

-Drop-down list A = Month (Jan-Dec)

-Drop-down list B = Category (all with individual names, but for simplicity let's just call them A-G)

-Drop-down list C = Zone (geographical regions of the world: W Europe, N America and S America, let's say)

Changing any of these drop down lists pulls through the relevant results. List A (the months) is higher in the heirarchy than lists B or C, which both use A to get their results. There are three different results pulled through:

-Baseline value (an absolute value using a simple lookup using INDEX/MATCH)

-Last month value (calculated by dividing the sum of a particular column by the sum of another particular column)

-Current month value (calculated by dividing the sum of a particular column by the sum of another particular column)

The above three results are column headers in the reports for each month. The problem is, that the column that these column headers occupy changes every week (but not the rows that they occupy).

For the first data retrieval, which is just the overall total for the months, I am using the following formula:

=INDEX(INDIRECT("'"&$B$3&"'!2:2"),MATCH(B6,INDIRECT("'"&$B$3&"'!1:1"),0))

In this formula, B3 contains the drop-down list of the months, B6 contains 'Current Value' (which is a column header on the various worksheets), row 1:1 contains the various column headings (as there are many more than just the ones I am aiming to retrieve) and row 2:2 contains the sums of those columns (on the 'months' worksheets).

So, this formula is returning the 'Current Value' for any given month for all categories and all zones. This part is the part I am doing OK with. The problem arises when I try to retrieve the 'Current Value' for, let's say, 'June', 'Category B' and, say, N America.

This is where I need to try and introduce SUMIF into my formula. In very simple words, what I want to do is tell Excel to do the following:

1. Search on the worksheet that is named in 'drop-down list A' for the position of the column entitled 'X' (column X divided by Y forms the 'Current Value' column as above)

2. Sum up the values in column 'X' that match 'Category B'. Let's call this figure FI

3. Sum up the values in column 'Y' that match 'Category B'. Let's call this figure FII

4. Divide FI/FII to create the contribution of 'Category B' in June towards 'Current Value'

The complication arises because the column position of 'X' and 'Y' changes month to month, so I need to incorporate additional INDEX/MATCH functions as well as the SUMIF function.

Tough one, I know, but I'm hoping someone here might have a good idea for how to get 'round it.

Thanks very much

James