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

You may be over-complicating things there. Take a look at this code:
http://www.rondebruin.nl/copy2.htm
Pay attention to this example:
Copy from row 2 till the last row with data

With all your data from all the sheets, summarized on one single sheet,
build a Pivot Table:
http://peltiertech.com/Excel/Pivots/pivottables.htm

Adjust the elements of the Pivot Table to suit your needs...that should
pretty much take care of things for ya...

Regards,
Ryan---


--
RyGuy


"andy62" wrote:

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