I get the Ref error only if a sheet name is mispelled or if one of the cells
that is supposed to have a sheet name is empty

"Tim" wrote:

Hi there,
I am using the following formula (see below), but wondering if the
sheetnames I enter have to be in the EXACT order that there are in the
spreadsheet?

=SUMPRODUCT(COUNTIF(INDIRECT("'"&BA2:BA151&"'!e50" ),"x"))

where BA2:BA151 is a range housing the relevant sheetnames in
separate cells ... AND e50 is the cell I am referencing in every sheet.

I get a #REF! error. Thanks