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?

Having your formula would help. The INDIRECT function will accomplish what
you want. But without your formula, I cannot tell what is wrong.

Tyro

"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?

Thanks for you reply tyro.
F7=G7
G7=September

Formula is:
=SUM(january:indirect($F$7)!B9)
I also tried
=SUM(january:indirect($G$7&!B9)) which creates an actual cell reference for
the indirect function

I can type =SUM(january:September!B9) and it works just fine
When I watched the execution of the first or second formula I get a name
error for January. The rest parses out fine but the name error overrides.

Steve

"Tyro" wrote:

Having your formula would help. The INDIRECT function will accomplish what
you want. But without your formula, I cannot tell what is wrong.

Tyro

"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?

What is cell B9?

Tyro

"SmilingSteve" wrote in message
...
Thanks for you reply tyro.
F7=G7
G7=September

Formula is:
=SUM(january:indirect($F$7)!B9)
I also tried
=SUM(january:indirect($G$7&!B9)) which creates an actual cell reference
for
the indirect function

I can type =SUM(january:September!B9) and it works just fine
When I watched the execution of the first or second formula I get a name
error for January. The rest parses out fine but the name error overrides.

Steve

"Tyro" wrote:

Having your formula would help. The INDIRECT function will accomplish
what
you want. But without your formula, I cannot tell what is wrong.

Tyro

"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?

You appear to be wanting to do double indirection. That is to say, go to
cell F7 to get the address G7 then go to cell G7 and get the worksheet name,
September.

Is that correct?

Tyro

"SmilingSteve" wrote in message
...
Thanks for you reply tyro.
F7=G7
G7=September

Formula is:
=SUM(january:indirect($F$7)!B9)
I also tried
=SUM(january:indirect($G$7&!B9)) which creates an actual cell reference
for
the indirect function

I can type =SUM(january:September!B9) and it works just fine
When I watched the execution of the first or second formula I get a name
error for January. The rest parses out fine but the name error overrides.

Steve

"Tyro" wrote:

Having your formula would help. The INDIRECT function will accomplish
what
you want. But without your formula, I cannot tell what is wrong.

Tyro

"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?

Tyro
I do not want to do the double indirect. It was the only way excel was
letting me get the function to work. a single indirect would be great.

B9 is the cell to be summed

"Tyro" wrote:

You appear to be wanting to do double indirection. That is to say, go to
cell F7 to get the address G7 then go to cell G7 and get the worksheet name,
September.

Is that correct?

Tyro

"SmilingSteve" wrote in message
...
Thanks for you reply tyro.
F7=G7
G7=September

Formula is:
=SUM(january:indirect($F$7)!B9)
I also tried
=SUM(january:indirect($G$7&!B9)) which creates an actual cell reference
for
the indirect function

I can type =SUM(january:September!B9) and it works just fine
When I watched the execution of the first or second formula I get a name
error for January. The rest parses out fine but the name error overrides.

Steve

"Tyro" wrote:

Having your formula would help. The INDIRECT function will accomplish
what
you want. But without your formula, I cannot tell what is wrong.

Tyro

"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?

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?

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?

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?

Excel's functions are just small programs written in a computer language
such as C++. Microsoft does not usually supply the source code for its
software so you'll probably never know "how and why" it works. You could get
some ideas on how things might work such as LOG which computes logarithms
and SIN which computes sines by looking at books on mathematics that show
formulas to do such things. In some cases, the hardware might have a single
instruction to do the function.

Tyro

"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.

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?

Biff
I have reviewed you equation and I have some questions.

the month function reduces to "01-september" and returns a value of 9. How
does "01-september" become a serial number equal to september?

The inner indirect function reduces to "1:9" which becomes $1:$9. Why does
this happen?

The rows function reduces to 9 which I think I understand.

The text function reduces to 252,"mmmm" which returns september. Why?

The outer indirect function reduces to "september!B9". Why do you use the
sumif function and the sumproduct function?

Thanks

"T. Valko" wrote:

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?