I need a formula for xl2003 to count instances of particular text across
multiple worksheets. HOWEVER, I do not know the names of the sheets. The
sheets are all survey responses, and will be dropped into one workbook by the
survey administrators. I was going to use the old "bookend" trick where I
set up dummy survey sheets named "Survey0" and "SurveyN" so they could drop
their actual survey sheets in between the bookends. Then my formulas would
reference that range:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&Survey0:SurveyN&" '!AD19"),TRUE))

But it seems like, to use this convention, I need to reference a range that
includes all the actual sheet names. Is there any way to do this in my
situation where the sheets are going to be named later? I know I could set
up IF formulas on each survey that convert the text to data that can be
summed in 3-D, but I'd rather not have to do that.

TIA