every other sheet is called Sheet1, Sheet 2, Sheet3... Sheet 99

Assume you enter the first formula in cell A2:

=COUNTA(INDIRECT("sheet"&ROWS(A$2:A2)&"!A1:A50"))

Copy down as needed.

--
Biff
Microsoft Excel MVP


"the-big-john" wrote in message
...
I have a situation where I want to reference multiple worksheets with the
same range. For instance I have a main SUMMARY page. And every other sheet
is
called Sheet1, Sheet 2, Sheet3... Sheet 99.

SUMMARY looks like:
Sheet_Number ; Number_of_inventory
Sheet1 ; COUNTA(Sheet1!$A$1:$A$50)
Sheet2 ; COUNTA(Sheet2$A$1:$A$50)
Sheet3 ; COUNTA(Sheet3!$A$1:$A$50)

I am trying to put COUNTA(Sheet1!$A$1:$A$50) so that it will give me the
number of items in each inventory sheet and put in on the SUMMARY page.

How do I make entry of the formula easier so that I don't have to click or
manually type out each worksheet because let's say I have many sheets, I'd
have to click on each worksheet to create the 3D reference. And I can't
have
it autocomplete by dragging the lower right hand corner... especially if
they
aren't named Sheet1...2 ...etc. Not that you could with 3D cell references
I
think. Can you create a custom list or do some weird cell referencing?