MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sum Kind of Problem


Posted by John L. on October 13, 2000 8:53 PM

I have a SUMIF formula that looks like this:
=SUMIF(September!G$2:G$13,D1,September!E$2:E$13)
There are a series of these formulas that together create
a financial report.

I would like to change sheet name September! reference, so
that it would reference a cell which could contain the
sheet name. Thus, in this cell people could type in the
name of the month and change the values in the report to
different months...so if they want March they would simply
type in March in the cell and it would reference a sheet
named March.

So far, I can not figure out how to make the formula
reference a cell.

Does anyone have any thoughts?


Posted by Ivan Moala on October 15, 2000 12:50 AM

This formula may help you Not fully tested

=SUMIF(INDIRECT(ADDRESS(2,7,,,C1)&":"&ADDRESS(13,7,,,)),D1,INDIRECT(ADDRESS(2,5,,,C1)&":"&ADDRESS(13,5,,,)))

Assumes the text month to change is in C1
ie. change c1 to October and it will reference
Octobers sheet.
Have a look @ online help for ;
Address & Indirect for a reference;

Ivan

Posted by John L. on October 15, 2000 9:51 AM

Ivan, worked great! You 'da man!