Referencing Sequential Sheets.
 

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?

Referencing Sequential Sheets.
 

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?

Referencing Sequential Sheets.
 

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?

Referencing Sequential Sheets.
 

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.

Referencing Sequential Sheets.
 

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.