sum across worksheets selected

  • Thread starter Thread starter mark v
  • Start date Start date
M

mark v

I have a number of worksheets (2-10), each named, all the same
structure with "y" or "n" placed in a number of cells.

I have the same structure on worksheet 1 but i want this to contain a
summary of whether any of the other worksheets contain a "y" in the
corresponding cell.

Sheet1(Summary) Worksheet2 Worksheet3 Worksheet4 etc
y n y y n y n y n n n n y n n n n n y n
y y y n n y n n n n y y y n n y y y n n


I also want to be able to have a list of check boxes on worksheet 1
which represent whether to include the corresponding worksheets in the
summary. i.e if only checkbox 2 and checkbox 3 are 'checked' then only
include worksheets2 and 3 in the summary calculation.

Is there any way of doing this without lots of nested IF statements
i.e. If checkbox2 AND worksheet2 cellx OR checkbox3 AND worksheet3
cellx etc..

Thank-you in advance for any help.
 
Hi


When you have the cells with "y"/"n" in columns A:E, starting from row 2, on
all sheets, and the sheet list in range Sheet1!G2:H10 (SheetName, Included,
where column Included has values TRUE/FALSE), then on sheet1 enter into C2
the formula:
=IF(OR((Sheet2!A2="y")*$H$2,(Sheet3!A2="y")*$H$3,(Sheet4!A2="y")*$H$4,...,(S
heet10!A2="y")*$H$10);"y";"n")
and copy it to needed range.


Arvi Laanemets
 
Back
Top