Using IF OR Function across multiple sheets

[Rachael]

Hi
Can anyone help me work out the following?
I have 9 sheets in the same workbook with cells variously populated with 0 or 1. In a separate sheet I want to fill a cell with "1" if 1 occurs in the same cell in any of the 9 sheets, or "0" if it doesn't occur at all. I have tried using the following:
=IF(OR('Training NetworkB'!C5="1",KnowledgeSharingBtxt!C5="1",'Encouragement NetworkB'!C5="1",'Organisation NetworkB'!C5="1",'Monitoring NetworkB'!C5="1",'Networking NetB'!C5="1",'Labour sharing NetworkB'!C5="1",'Conflict NetworkB'!C5="1",'Phys_Finan Capital NetB'!C5="1"),"1","0")
It returns a 0 in all cells, even if a 1 occurs.
Many thanks in advance!

 

[MrTeeny]

You need to remove the quotations marks from your checks as it's treating the 1 and 0's as text rather than numbers.

Training NetworkB'!C5="1" should be Training NetworkB'!C5=1 assuming you are checking only for numbers

 

[Rachael]

You're a superstar! Thanks MrTeeny.

 

[Dave Patton]

deleted

 

[Dave Patton]

Excel 2010
	A	B	C
23	TRUE	1
24	TRUE	1
1a

Cell Formulas
Range	Formula
A23	=(SUM('1c:2aa'!C50)>0)
A24	=(SUM(First:Last!C50)>0)
B23	=(SUM('1c:2aa'!C50)>0)+0
B24	=(SUM(First:Last!C50)>0)+0

You can sum across a range of sheets.
N.B. My examples do not include your sheet names or cell references.

One way is to insert blank sheets named First and Last before and after relevant sheets.

The same concept can work with existing names but First and Last is neater.

Sum > 0 yields True or False and adding 0 converts to 1 or 0