indirect reference to a range of worksheet

[joseph168]

I have six worksheets in sequence from "July to Dec". In each worksheet I have data enetered into the cell A1

The following formula works fine =SUM(July:Dec!A1) to sum all the A1 cells at all worksheets

How do I use an indirect reference to two different cells; so I can change the start and the ending month in
the above formula. Rather than hardcoded at the formula,

 

[Trebor76]

Hi joseph168,

Welcome to MrExcel!!

Unfortunately one of the limitations with the INDIRECT function is that it doesn't work on 3D formulas. That said, you can simply adjust your existing formula to incorporate the second cell, i.e. the following formula...

=SUM(July:Dec!A1:A5)

...will return the sum of the five cells in each of the sheets.

HTH

Robert

 

[joseph168]

Hi joseph168,

Welcome to MrExcel!!

Unfortunately one of the limitations with the INDIRECT function is that it doesn't work on 3D formulas. That said, you can simply adjust your existing formula to incorporate the second cell, i.e. the following formula...

=SUM(July:Dec!A1:A5)

...will return the sum of the five cells in each of the sheets.

HTH

Robert

Hi Robert,

Thanks for your suggestion!

Why this Indirect function works =SUM(INDIRECT(A17&"!a20:a24")) I entered the worksheet name at cell A17?
What was the major difference between this Indirect function from my previous case?

Many thanks for your help!

Joseph

<colgroup><col style="width: 48pt;" span="4" width="64"> <tbody>
</tbody>

 

[Trebor76]

Why this Indirect function works =SUM(INDIRECT(A17&"!a20:a24")) I entered the worksheet name at cell A17?
What was the major difference between this Indirect function from my previous case?

Because as there's only one tab name in cell A17, it's no longer a 3D formula which the INDIRECT function is fine with.

 

[T. Valko]

I have six worksheets in sequence from "July to Dec". In each worksheet I have data enetered into the cell A1

The following formula works fine =SUM(July:Dec!A1) to sum all the A1 cells at all worksheets

How do I use an indirect reference to two different cells; so I can change the start and the ending month in
the above formula. Rather than hardcoded at the formula,

If you enter the month numbers in the cells then you can do this.

The sheet names must all be in the same format. All must be in the short name format like Jan, Feb, Mar or all must be in the long name format like January, February, March.

Let's assume you have 12 sheets, 1 for each month using the short month name mmm.

A1 = month number from 1 to 12 for the starting sheet
B1 = month number from 1 to 12 for the ending sheet

Then:

=SUMPRODUCT(SUMIF(INDIRECT(TEXT(ROW(INDIRECT(A1&":"&B1))*29,"mmm")&"!A1"),"<1E100"))

 

[christiancastillomoran]

If you enter the month numbers in the cells then you can do this.

The sheet names must all be in the same format. All must be in the short name format like Jan, Feb, Mar or all must be in the long name format like January, February, March.

Let's assume you have 12 sheets, 1 for each month using the short month name mmm.

A1 = month number from 1 to 12 for the starting sheet
B1 = month number from 1 to 12 for the ending sheet

Then:

=SUMPRODUCT(SUMIF(INDIRECT(TEXT(ROW(INDIRECT(A1&":"&B1))*29,"mmm")&"!A1"),"<1E100"))

Hi, I know I'm reviving an old thread, but how can this formula be adapted if my tab names have the month and year in the following format: Jan '16, Feb '16, Mar '16 ...etc? I imagine the date format is "mmm 'yy". Any reply wlil be greatly appreciated

It is a pleasure to be a member here as well.