indirect formula does not work

[popipipo]

I want to translate this formula

=SUM('week 1:week 4'!A1)

in the indirect form

Why does this indirect formula don't work

=SUM(INDIRECT("'week "&A1&":week "&B1&"'!A1"))


Willem

 

[Aladin Akyurek]

I want to translate this formula

=SUM('week 1:week 4'!A1)

in the indirect form

Why does this indirect formula don't work

=SUM(INDIRECT("'week "&A1&":week "&B1&"'!A1"))


Willem

Enter the sheet names in A1:D1 then invoke:

=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:D1&"'!A1"),{"<0",">0"}))

 

[popipipo]

Ok, this works
But i have only 2 cells
A1 for the start week en B1 voor the end week .

And the number of weeks is different.
It could be 2 but also 4 or 8

 

[Aladin Akyurek]

Ok, this works
But i have only 2 cells
A1 for the start week en B1 voor the end week .

And the number of weeks is different.
It could be 2 but also 4 or 8

Enter the sheet names in column A from A1 on downwards...

Now invoke:

=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:INDEX(A:A,MATCH(REPT("z",255),A:A))&"'!A1"),{"<0",">0"}))

Even better if you define SheetList (using Insert | Name | Define or Formulas | Name Manager) as referring to:

=Sheet1$A$1:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))

The formulas assumes Sheet1 as the sheet where the sheet names are kept. Adjust this to suit.

The formula now becomes:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A1"),{"<0",">0"}))

 

[popipipo]

The result not the right.

The result is the value of the second sheet.

 

[T. Valko]

I want to translate this formula

=SUM('week 1:week 4'!A1)

in the indirect form

Why does this indirect formula don't work

=SUM(INDIRECT("'week "&A1&":week "&B1&"'!A1"))


Willem

Try this...

Just enter the week numbers in cells A1 and B1.

If you want to sum from Week 1 to Week 3 then:

A1 = 1
B1 = 3

Then, the formula is:

=SUMPRODUCT(SUMIF(INDIRECT("'Week "&ROW(INDIRECT(A1&":"&B1))&"'!A1"),"<1E100"))

 

[popipipo]

The last one does I want to do

Thanks

 

[T. Valko]

The last one does I want to do

Thanks

You're welcome. Thanks for the feedback!

 

[Aladin Akyurek]

The result not the right.

The result is the value of the second sheet.

Let's have the following 3 sheets...

Week 1

A1: 3

Week 2

A1: 7

Week 3

A1: 10

Let Sheet1 be the sheet we do the processing...

Let A1:A3 house the relevant sheet names:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>Week 1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Week 2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Week 3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19> </TD></TR></TBODY></TABLE>

In C1 enter:

=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:INDEX(A:A,MATCH(REPT("z",255),A:A))&"'!A1"),{"<0",">0"}))

will return 20 as required.

Entering additional sheet names in column A will also be processed immediately.