Indirect Formula Syntax for referring to a range on two Worksheets

[jscranton]

B1=Name of WorkSheet
B2=Name of 2nd Worksheet

I am trying to write a sum formula using Indirect so that the end-user can enter the names of the sheets in B1 & B2 (the values are always in cell F5 on each sheet). I thought this would work but it is throwing a REF# error.

=SUM(INDIRECT("'"&B1&":"&B2&"'!F5"))

with some added spaces for clarity:

=SUM(INDIRECT(" ' " & B1 & " : " & B2 & " ' ! F5"))

 

[T. Valko]

So between the Start and End Sheets are sheets with dates. Say January 1 to February 1. There is obviously an easy formula that can calculate the total from Start to End (=SUM(Start:End!F5). The goal was to be able to allow the end-user to select a sub-range of the total and calculate the sum from say Jan 15 to Jan 25. (Sheet names: 1.15.2012 to 1.25.2012)

Does that make sense?

Yes.

I have to step out for a few hours but I'll show you how this can be done when I get back later this afternoon (unless someone else jumps in).

 

[T. Valko]

Yes.

I have to step out for a few hours but I'll show you how this can be done when I get back later this afternoon (unless someone else jumps in).

Try this...

Sheet names in the format m.d.yyyy like:

1.1.2012
1.2.2012
1.3.2012
1.4.2012

B1 = a true Excel date as the starting sheet. For example: 1/1/2012

B2 = a true Excel date as the ending sheet. For example: 1/3/2012

Then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT(B1&":"&B2)),"m.d.yyyy")&"'!F5"),"<1E100"))

 

[Domski]

That would have been my suggestion but I'm guessing they would also need to dynamically list the sheet names they require to be included as well.

Dom

 

[T. Valko]

That would have been my suggestion but I'm guessing they would also need to dynamically list the sheet names they require to be included as well.

Dom

The range of sheet names to be included in the calculation are in B1:B2. I'm assuming they want the calculation on a contiguous range of sheets. For example, 1/1/2012 thru 1/5/2012. Not like: 1/1/2012 and 1/4/2012 and 1/17/2012.

 

[Domski]

My mistake. I thought you still had to list the sheets.

Not something I've ever had the need to play with but good to know .

Dom

 

[T. Valko]

My mistake. I thought you still had to list the sheets.

Not something I've ever had the need to play with but good to know .

Dom

If the sheet names are random like:

North
East
South
West

Then, yes, you'd have to generate an array of the sheet names. This can be done in either a range of cells or directly in the formula but might not work in this application:

http://www.mrexcel.com/forum/showth...t-names-using-a-formula&p=2118912#post2118912

But, if the sheet names follow some sort of sequential naming pattern then we can "build" the sheet names directly into the formula as was done in this application.

 

[Domski]

Cheers Biff. Familiar with that thread of yours .

Dom

 

[T. Valko]

Cheers Biff. Familiar with that thread of yours .

Dom

 

[jscranton]

Thanks to Both.

Bill, your suggestion above worked. I will circle back if the "random" sheet name issue arises but I think I can handle that.

 

[T. Valko]

Thanks to Both.

Bill, your suggestion above worked. I will circle back if the "random" sheet name issue arises but I think I can handle that.

Good deal. Thanks for the feedback!