One approx way might be to set it up along these lines:
Listed across in B1:F1 are
the sheetnames: Sheet1, Sheet2 ... Sheet5
In the row beneath the sheetnames, in B2:F2
are data validations (DVs) to select "Yes" or "No"
To set up the DVs, just select the range B2:F2,
Click Data Validation, select List (under "Allow")
Put in the source box: Yes, No
Click OK
Listed in cell A3 down would be the cell references
to be summed in the sheets, e.g.: A1, A2, etc
Put in B3: =IF(B2="Yes",INDIRECT(B1&"!"&$A3),"")
Copy across to F3
Put in G3: =SUM(B3:F3)
Select B3:G3, fill down
Cols B to F will extract the values for the cell refs in col A
from the sheets flagged "Yes" in B2:F2
and Col G will give the required sums of these values
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Steve W" wrote in message
...
I have a workbook where I want to be able to exclude the cells in the
summing of one or more sheets based on some logic.
The solution needs to be a dynamic formula based on a flag such as Yes or
No
(include or exclude the sheet)
eg how could I vary the formula below if I didnt want to get the values of
A1 from Sheet5 and Sheet9 in the summed total
=SUM(Sheet2:Sheet11!A1)
is there some smart variation of SUMIF that could apply that would refer
to
say a table of Sheet names with a Yes / No flag next to each Sheet name?
Steve W
|