INDIRECT SUM WITH SHEET NAMES

[occy]

I am trying to sum a particular cell across a range of worksheets. My formula is:

=SUM(INDIRECT(K4&":"&K5&"!"&"J60"))

Where K4 contains the name of the first worksheet and K5 contains the name of the last worksheet in the range I want to sum. J60 is the cell I want to sum.

I get the dreaded #ref! result. Help would be much appreciated.

Thanks

 

[occy]

Thanks for the explanation.

 

[asusetyo]

Guys,

I am trying to do the exact formula proposed within 158 Sheets (I am using excel 2010),
It gives me message

 Mircosoft Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you. Try editing the last formula you entered or remobing it with the Undo command.

is there something wrong with the excel version?

 

[asusetyo]

Guys,

I am trying to do the exact formula proposed within 158 Sheets (I am using excel 2010),
It gives me message

 Mircosoft Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you. Try editing the last formula you entered or remobing it with the Undo command.

is there something wrong with the excel version?

Ahh, I see, I know why, because I tried to SUM in the same cell address so it came out circular reference, is it possible to do this formula within the same cell address?

Thanks

 

[Aladin Akyurek]

Ahh, I see, I know why, because I tried to SUM in the same cell address so it came out circular reference, is it possible to do this formula within the same cell address?

Thanks

What is the formula you are trying? Is it really related to the original thread here?

 

[asusetyo]

Yes, i change the "which cell" to B7 (on the same cell the formula typed),

if there're Sheets("1","2","3") that have value 1 on Cell("A1"), i want to return the sum on the Sheet("ALL") on the same cell too (A1).

 

[Aladin Akyurek]

Yes, i change the "which cell" to B7 (on the same cell the formula typed),

if there're Sheets("1","2","3") that have value 1 on Cell("A1"), i want to return the sum on the Sheet("ALL") on the same cell too (A1).

What is the formula you did try and in which cell?

 

[asusetyo]

What is the formula you did try and in which cell?

Sorry seems I got lost.
I am using the same Example that Juan gave, with the same formula

 =SUM(IF((MATCH(Sheets,Sheets,0)>=$C$3)*(MATCH(Sheets,Sheets,0)<=$C$4),SUBTOTAL(9,INDIRECT("'"&Sheets&"'!"&$B$5)))) 

but I pasted the formula above in cell A1

 

[Aladin Akyurek]

Sorry seems I got lost.
...

Care to describe your own problem in terms of just 3 sheets?

 

[asusetyo]

Okay,

Let say we have 4 sheets

Sheets ("1","2","3") and Sheets ("ALL") which is the summary of Sheets ("1","2", & "3")

So basically all number in the same cell will be summarized to Sheet ("ALL")
Let say on

Sheet("1").Cell("A1") = 2
Sheet("2").Cell("A1") = 3
Sheet("3").Cell("A1") = 4

The formulation on Sheet("ALL").Cell("A1") should be 9, but it turn ZERO, when i press SHIFT+CTRL+ENTER it shows the warning sign of circular reference.
Is it clear?

 

[Aladin Akyurek]

Okay,

Let say we have 4 sheets

Sheets ("1","2","3") and Sheets ("ALL") which is the summary of Sheets ("1","2", & "3")

So basically all number in the same cell will be summarized to Sheet ("ALL")
Let say on

Sheet("1").Cell("A1") = 2
Sheet("2").Cell("A1") = 3
Sheet("3").Cell("A1") = 4

The formulation on Sheet("ALL").Cell("A1") should be 9, but it turn ZERO, when i press SHIFT+CTRL+ENTER it shows the warning sign of circular reference.
Is it clear?

Are you trying it in VBA?