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

 

[kpark91]

=sum(indirect(b1 & "!f5"), indirect(b2 & "!f5"))

 

[T. Valko]

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

Try it like this:

=INDIRECT("'"&B1&"'!F5")+INDIRECT("'"&B2&"'!F5")

 

[jscranton]

Thanks kpark. So the syntax is like VBA for named ranges? Makes sense.

 

[Domski]

If it's just 2 sheets then kpark91's solution will work however if you want a range of sheets to be included in the sum formula then you can't just use indirect to create the range reference but would need to list the sheet names and wrap the whole thing in SUMPRODUCT.

Dom

 

[jscranton]

So, if the workbook has, for example, 5 worksheets with the first being "Start" and the last being "End", then what you are saying is that kPark's will only sum the two named sheets rather than the range?

Would T. Valko's formula have the same problem?

 

[Domski]

I believe so.

Dom

 

[T. Valko]

So, if the workbook has, for example, 5 worksheets with the first being "Start" and the last being "End", then what you are saying is that kPark's will only sum the two named sheets rather than the range?

Would T. Valko's formula have the same problem?

Is that what you want to do? Sum cell F5 in all the sheets from Start to End? If so, then what is the purpose of letting users enter sheet names?

It may be easier to just use a simple link formula then get the sum of the linked cells.

 

[jscranton]

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?

 

[Domski]

Makes sense but not sure how you would achieve it.

Dom