On Sat, 23 Apr 2011 02:21:44 -0700 (PDT), bob
wrote:


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.

Hi Bob,
This is indeed what my original question was.

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.

thanks a lot.
The point is I already solved the issue, with a two step procedure,
but I was wondering if there is a single shot to do it. Just for sake
of knowledge and curiosity.
This is the real problem.
I have 10 projects, which all together create a FUND. Projects are not
yet final, so i need to have ON/OFF (on the "Assumptions" worksheet)
and make some simulation to understand which is worth having or not.
I have the 10 worksheet, named 1 to 10, where I have only calculation
referring to that specific project. There are different sources of
revenues (REV 1, REV 2, REV3) and Cost (Cost 1, 2,3,4) and other items
for each projects.
Then I have FUND worksheet, where I do all the SUM but for the ACTIVE
projects only. I have the different Revenue lines and cost lines which
have to show the total, year by year, for the ON projects only. So I
needed a formula doing: go to worksheet 1, and if the project is ON,
then see how much is this revenue line during this year and go to
other 9 worksheets and check the same.
The only way I could make it was to create another worksheet for
"dirty calculations" and "gross summary": I created a table for each
Revenue line and each cost line. In each table I have the 10 project,
and the first column tells me if they are active or not. So now I can
-in my FUND page- use the Conditional SUM: if the criteria range is ON
(in the Summary sheet) then sum that year line of revenue for the ON
projects.