hi all,

too bad, no way to do it with sumproduct,
because Excel doesn't accept this expression {'1'!A1, '2'!A1, '3'!A1} as being a matrix,
i doesn't see a way to do it, except with custom Function.

--
isabelle

Le 2011-04-23 05:21, bob a écrit :
On Apr 21, 6:50 pm, wrote:
hi Domanda,

I see no other solution than a custom Function

=MySum(A1:A10,A1)

Code:

 Function MySum(MyRangeSheetsName As Range, OneRange As Range)
 For Each rng In MyRangeSheetsName
 MySum = MySum + Sheets(CStr(rng.Value)).Range(CStr(OneRange.Address))
 Next
 End Function

--
isabelle

Le 2011-04-21 13:20, Domanda a écrit :



On Thu, 21 Apr 2011 07:22:58 -0400, wrote:

hi,

In worksheet 11, if the names are in column A and the on / off in column B

=IF(B1="on",INDIRECT(A1&"!A1"),0)
fill down

Isabel....not what I asked I think.
I use this formula, but here I am asking to sum A1 in each worksheet
just using one cell.
I need a single formula making the calculations and check of
conditions ACROSS worksheets, not 10 formulas and then sum them up.- Hide quoted text -

- Show quoted text -

Hi Domanda,

If Isabell or someone knows how to make a sheet array =SUM(IF($A$1:$A
$10="on",1!A1:10!A1)) work, then it can be done in one cell. ELSE,
what I think you want to do Domanda, can be done in two steps. Enter
=SUM(IF($A$1:$A$10="on",1!$A1)) in Col [B1 to B10]. Fill down won't
work without changing 1to10 before "!$A1". Then simply do =sum(b1:b10)
in a cell for the total of cell "A1" in sheets selected with "ON".
Hope this helps, I tested it with three sheets without using a
volitile function like INDIRECT with it working fine.
INDIRECT works with filldown by designating sheets 1 through 10 in a
separate column and referencing it. Certainly there is a better way to
accomplish your goal, but this should get you by for now - unless I
goofed somewhere.