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

 

[jscranton]

=IF(ISBLANK('Review Team'!D8),"",SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT($B$1&":"&$B$2)),"mm.dd.yy")&"'!g"&(ROW(G5))),"<1E100")))


Troubleshooting this again. So, the end user changed the formula a bit. See the portion in blue which is now causing a reference error (as one would expect). So the Reviewer (aka Employee) referenced at 'Review Team'!D8 has productivity stats in cell G5 of every dated worksheet. The next Reviewer/Employee at 'Review Team'!D9 would have stats at G6 and so on.

I am trying to think of way to still use indirect so that the Sumproduct formula will sum every cell between the dates specified in cells B1 and B2.

Does that question make sense?

 

[Aladin Akyurek]

=IF(ISBLANK('Review Team'!D8),"",SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT($B$1&":"&$B$2)),"mm.dd.yy")&"'!g"&(ROW(G5))),"<1E100")))


Troubleshooting this again. So, the end user changed the formula a bit. See the portion in blue which is now causing a reference error (as one would expect). So the Reviewer (aka Employee) referenced at 'Review Team'!D8 has productivity stats in cell G5 of every dated worksheet. The next Reviewer/Employee at 'Review Team'!D9 would have stats at G6 and so on.

I am trying to think of way to still use indirect so that the Sumproduct formula will sum every cell between the dates specified in cells B1 and B2.

Does that question make sense?

Either...

=IF(ISBLANK('Review Team'!D8),"",
  SUM(SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT($B$1&":"&$B$2)),"mm.dd.yy")&
    "'!"&CELL("address",G5)),{"<0",">0})))

Or...

=IF(ISBLANK('Review Team'!D8),"",
  SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT($B$1&":"&$B$2)),"mm.dd.yy")&
    "'!"&CELL("address",G5)),"<"&BigNum))

where BigNum is defined as referring to: 9.99E+307.

 

[jscranton]

So, seems to be working but with this error:

if the date is $B$1 and $B$2 are the same (so they reference the same worksheet), the formula(s) work fine. However when the dates refer to two worksheets, there is an error.

So if $B$1 = 7/13/2012 and $b$2 = 7/13/2012, the formula correct sums the values found in G5 on the worksheet titled "7.13.12." However, if $b$1 = 7/13/2012 and $b$2 = 7/14/2012, then it triggers the reference error.

 

[T. Valko]

=IF(ISBLANK('Review Team'!D8),"",SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT($B$1&":"&$B$2)),"mm.dd.yy")&"'!g"&(ROW(G5))),"<1E100")))


Troubleshooting this again. So, the end user changed the formula a bit. See the portion in blue which is now causing a reference error (as one would expect). So the Reviewer (aka Employee) referenced at 'Review Team'!D8 has productivity stats in cell G5 of every dated worksheet. The next Reviewer/Employee at 'Review Team'!D9 would have stats at G6 and so on.

I am trying to think of way to still use indirect so that the Sumproduct formula will sum every cell between the dates specified in cells B1 and B2.

Does that question make sense?

Try this...

=IF('Review Team'!D8="","",SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT($B$1&":"&$B$2)),"mm.dd.yy")&"'!"&CELL("address",G5)),"<1E100")))

 

[jscranton]

Same issue. I am going to let this one go for now. As always, I really appreciate the help.

 

[T. Valko]

Same issue. I am going to let this one go for now. As always, I really appreciate the help.

Works OK for me.

=IF('Review Team'!D8="","",SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT($B$1&":"&$B$2)),"mm.dd.yy")&"'!"&CELL("address",G5)),"<1E100")))

In my test file...

Review Team D8 = X

B1 = 7/1/2012
B2 = 7/3/2012

Sheets:

07.01.12 G5 = 1
07.02.12 G5 = 10
07.03.12 G5 = 2
07.04.12 G5 = 100

The above formula returns the correct result of 13.

Want me to post the file?

 

[jscranton]

I trust you. It must mean the error is somewhere else. Let me circle back to the end-user and see if I can ferret out the issue.

 

[rasko]

thank you for that, its very useful for me

what if sheet names are monthly (jan 18,feb 18,...) ; and i would like to sum for between mar 18 to sep 18 ?

and what can i do for calculate for average for same cell in multiple sheets? (exclude zeros and blanks, if any) "averageif" doesnt work for that.

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

 

[Aladin Akyurek]

thank you for that, its very useful for me

what if sheet names are monthly (jan 18,feb 18,...) ; and i would like to sum for between mar 18 to sep 18 ?

and what can i do for calculate for average for same cell in multiple sheets? (exclude zeros and blanks, if any) "averageif" doesnt work for that.

Let B1 house 2018-03-01 and B2 2018-09-01.

If the sheet names are text like Jan 18, Feb 18, etc., create a range which houses these sheet names one by one. Name this range SheetList via the name range.

Define BigNum in the Name Manager as referring to:

=9.99E+307

In C1 enter:

=MATCH(TEXT(B1,"mmm yy"),SheetList,0)

In C2 enter:

=MATCH(TEXT(B2,"mmm yy"),SheetList,0)

Now invoke for summing G5 of Jan 8 to Sep 18...

=SUMPRODUCT(SUMIF(INDIRECT("'"&OFFSET(SheetList,$C$1-1,0,$C$2-$C$1+1)&"'!"&CELL("address",$G5)),"<"&BigNum))

For averaging...

=SUMPRODUCT(SUMIF(INDIRECT("'"&OFFSET(SheetList,$C$1-1,0,$C$2-$C$1+1)&"'!"&CELL("address",$G5)),"<"&BigNum))/SUMPRODUCT(COUNTIF(INDIRECT("'"&OFFSET(SheetList,$C$1-1,0,$C$2-$C$1+1)&"'!"&CELL("address",$G5)),">0"))