# List worksheets if cell S1<>0



## tezza (Dec 14, 2022)

Hi

I'm currently using the formula below to count how many times the Cell S1 doesn't contain a zero


```
=SUM('Book 1:Book 10'!S1)
```

How do I actually list any worksheets where S1 doesn't contain a zero?

EG: Book 1, Book 6, Book 10 don't have a 0 in Cell S1


----------



## Sufiyan97 (Dec 19, 2022)

Try

Read and apply name






						How To Generate A List Of Sheet Names From A Workbook Without VBA | How To Excel
					






					www.howtoexcel.org
				




Then Try

Book6ABCD11Sheet1 22Sheet2 33Sheet3 44Sheet4 55Sheet5 66Sheet6 77Sheet7 88Sheet8Sheet899Sheet9 1010Sheet10Sheet1011Sheet11Cell FormulasRangeFormulaB1:B10B1=INDEX(SheetNames,A1)C1:C10C1=IF(AND(INDIRECT(B1&"!s1")<>0,INDIRECT(B1&"!s1")<>""),B1,"")


----------



## Fluff (Dec 19, 2022)

Another option
	
	
	
	
	
	



```
=FILTER("Book "&SEQUENCE(10),VSTACK('Book 1:Book 10'!S1)<>0)
```


----------



## tezza (Dec 19, 2022)

Thank you. 

Don't think 2010 has filter?


----------



## Sufiyan97 (Dec 19, 2022)

Without helper column

Book6ABC11 22 33 44 55 66 77 88Sheet899 1010Sheet10111213Sheet11Cell FormulasRangeFormulaB1:B10B1=IF(AND(INDIRECT(INDEX(SheetNames,A1)&"!s1")<>0,INDIRECT(INDEX(SheetNames,A1)&"!s1")<>""),INDEX(SheetNames,A1),"")


----------



## Fluff (Dec 19, 2022)

tezza said:


> Don't think 2010 has filter?


No but 365 does & you are showing that in your profile.


----------

