counting the yes ansewrs in multiple excel sheets

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

Hi Mate,

thanks for the reply. Have tried that an still having problems as what I am specifically trying to do is count if there is a yes in the same cell in different worksheets. So it could be cell A5 in a worksheet called "white", A5 in "black" A5 in "red" and A5 in "blue". I have drop downs in the worksheets that allow you choose yes or no and I want a counter in the first worksheet "Dash" to say how many yes options have been chosen. I want to do this with a large amount of cells and sheets throughout the workbook. Hope this helps. Thanks again

Hi Tony,

sorry, I finally got it working and was going to message you saying so. Thanks very much for your help

Thanks again Tony