If you'd like, I can put together a sample file that demonstrates both
methods I suggested.


--
Biff
Microsoft Excel MVP


"SmilingSteve" wrote in message
...
Biff
Thanks for your input. I am not intimately familiar with a lot of excel
formula so I will need time to understand what you suggest. I will get
back
to you.

Is there a good reference for how excel processes its functions? Help
gives
the format and explanations of variables but not the how and why it works.

"T. Valko" wrote:

Ooops!

Forgot something:

Enter this formula in J1:
Enter this formula in K1:

Now, select both J1 and K1 and copy down to J12:K12


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
G7=September

Assuming you will *always* want the sum from January to ???

Try this:

=SUMPRODUCT(SUMIF(INDIRECT(TEXT(ROW(INDIRECT("1:"& MONTH("01-"&G7)))*28,"mmmm")&"!B9"),"<1E100"))

If that's too "obfuscated" then:

Enter this formula in J1:

=TEXT(ROWS(J$1:J1)*28,"mmmm")

Enter this formula in K1:

=INDIRECT(J1&"!B9")

Then your sum formula would be:

=SUM(K1:INDEX(K1:K12,MATCH(G7,J1:J12,0)))



--
Biff
Microsoft Excel MVP


"SmilingSteve" wrote in
message
...
I have an excel workbook with multiple work sheets (Summary, January,
february..., December) On the Summary worksheet I want to create a 3d
reference summing all B9 cells across the monthly worksheets. I can
do
this
for a specified range of months, like =sum(january:December!B9. I
want
the
second reference, December, to be variable, like february or
september.

I tried indirect but it evaluates the string left to right and
consequently
gives me a name error.

Any suggestions as to how I do this?