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?

Since all of the data is in the same group of cells on each page just type
=counta( and then shift-click on the last tab, this will select all of them
and you will see Sheet1:Sheet99! and then you select your range, that should
do it. with the final ) of course.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"the-big-john" wrote:

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?

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?

T. Valko's post was most helpful. I ended up using something along the lines
of:
=COUNTA(INDIRECT(A2&"!A2:A50000"))
I knew about the Indirect function. I didn't know about the ampersand and
quotations.
Thanks to all.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"the-big-john" wrote in message
...
T. Valko's post was most helpful. I ended up using something along the
lines
of:
=COUNTA(INDIRECT(A2&"!A2:A50000"))
I knew about the Indirect function. I didn't know about the ampersand and
quotations.
Thanks to all.