Countif across multiple sheets - sheet NAME issue?

[Tim]

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

 

[Duke Carey]

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]

I have extra sheets at the beginning and end of the 'named' sheets ... would
that have anything to do with it do you think?

 

[T. Valko]

I have extra sheets at the beginning and

end of the 'named' sheets

What does "extra" sheets mean?

If the sheets don't exist yet but you have their names listed in the range
then you'll get the #REF! error.

 

[Tim]

Hi there ... these are just additional sheets that are not apart of the
'named' sheets. I have a couple at the beginning of the spreadsheet ... then
150 named sheets ... then a couple extra sheets at the end of the
spreadsheet. Didn't know if they would cause the issue.....

 

[Duke Carey]

'Extra' sheets wouldn't have any influence on the issue. I suspect you have
one or more names in your list of sheets that has an extra space at the end,
or is mispelled in some other fashion, or maybe even references a sheet that
doesn't exist.

You could put a formula in an empty column next to the list of sheet names
to check on the validity of the name

=INDIRECT("'"&[cell with first name]&"'!A1")

Copy that down to the end of your list. If one or more of them gives you
the Ref error, you have a pointer to the problem.