Dynamically Variable Ranges

In my worksheet cell A1 represents a financial reporting period from 1 to 12. It also represents the number of columns I need to sum in order to arrive at the desired period totals in this income statement worksheet. How can I get Excel to automatically change my SUMPRODUCT function as the value in cell A1 is changed from 1 to 12?

Here is a sample cell formula:

=SUMPRODUCT(ISNUMBER(MATCH(GL1SHL.XLS!$A$2:$A$332, {"4620","5220"},0))*(GL1SHL.XLS!$CE$2:$CE$332)*(GL1SHL.XLS!$I$2:$T$332))

The above formula represents the totals for period 12 for General Ledger accounts 4620 and 5220.

GL1SHL.XLS! references the workbook that has the financial data exported from my G/L application.

$A$2:$A$332 is the column which contains the G/L account number to be matched.

$CE$2:$CE$332 references a cell that resolves to the value 1 or -1 so that expenses which are exported as positive values become negative.

$I$2:$T$332 represents the 12 columns and 332 rows that contain all the financial data necessary to calculate the income for period 12 with each row representing a different G/L account's 12 periods of financial data. I need a method to dynamically replace the 'T' in $I$2:$T$332 and resolve it based on the value in cell A1. So if A1=1 then 'T' becomes 'I' and if A1=2 then 'T' becomes 'J' etc.

I would like to do this as a macro substitution instead of having to make 12 worksheets, one for each period, or instead of having 12 nested loops.

Thanks
Pinny

Inserting the following: (INDIRECT("$I$2:$"&CHAR(72+A1)&"$332")

in:
=SUMPRODUCT(ISNUMBER(MATCH(GL1SHL.XLS!$A$2:$A$332, {"2120","4010"},0))*(GL1SHL.XLS!$CE$2:$CE$332)*(GL1SHL.XLS!(INDIRECT("$I$2:$"&CHAR(72+A1)&"$332")))

returns an error directing me to Excel's help capability which isn't always that helpful.

Did I miss something on the syntax?
Any ideas?

Thanks
Pinny

Thanks VBA. I will test it fully but it looks goods. Your help and quick response is most appreciated. By the way, what would be the best way to limit the input of the period indicator cell A1 between the values of 1 and 12?

All the best to you.
Pinny

Thanks again VBA. Any good links to charting that I can use with the results of my income statement so the results can also be visual?

Thanks
Pinny

VBA you are a veritable encyclopedia of information. You have saved me a lot of time. I feel empowered. Much appreciated.

Pinny

Bryan

Here is a sample cell formula that works without naming a sheet in the formula syntax of my template workbook which references my GL1SHL external data workbook.

Please bear in mind that this is the formula that Excel displays when both template and data workbooks are first opened and you haven't made any changes to your referencing cell formula.

=SUMPRODUCT(ISNUMBER(MATCH(GL1SHL.XLS!$A$2:$A$332, {"2120","4010"},0))*(GL1SHL.XLS!$CE$2:$CE$332)*(INDIRECT("GL1SHL.XLS!$I$2:$"&CHAR(72+A1)&"$332")))

In my GL1SHL workbook there is only one sheet which I also named GL1SHL. May be that's the difference.

But I did notice that when I changed the only sheet named in the GL1SHL workbook from GL1SHL to TEST that Excel then automatically references the external data in my template workbook with the syntax you suggested. [workbook]sheet!

Would you agree with me that the best practice would be to always use the [workbook]sheet! syntax for both clarity and to insure that Excel always finds the data you are trying to reference?

Thanks
Pinny

You will never go wrong by always making a complete reference (workbook, worksheet, and range). This is true both for in-cell formulas and for VBA code (macros).

But, there is a limit to the amount of text you can put into a single cell. [Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.]

I suspect that is rarely a problem.