Hi folks,

I am trying to count the amount of times a yes answer is provided over a number of worksheets, I am using this formula
=SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3,4,5,6,7,8}&"!D4"),"Yes"))
so far so good but once I change the name of any of the worksheets it automatically gives me a #REF! in the cell. Is there a way of renaming all the sheets and still have this work.
Any help would be seriously appreciated.

Thanks