I have 12 worksheets with different names (I wish, I didn't change names
from Sheet[numbers] *)
I want to perform counta command on column A of each sheet in my reference
sheet.
With a Macro (Given by Gord Dibben) I was able to produce a list of sheet
names in my reference sheet.
Now I dont know how to address these names in counta command. On each sheet
it will be simply: counta(A:A)

Also is there any resources in Internet explaining about dealing with
calculation in different sheets.

*) I think if I leave name of sheets unchanged (Sheet[numbers]) then it will
be easy to use sheet names in commands. Isn't it so?

I find following solution. Although it is not a robust solution but solves
my problem.
If anybody else finds a better solution, let me know. Solution:
Assuming the sheetname is in a1
b1=COUNTA(INDIRECT(ADDRESS(1,1,4,TRUE,A1)):INDIREC T(ADDRESS(100,1,4,TRUE,A1)))

This solution assumes number of rows in each sheet is not more than 100. it
could be adjusted for any range.
Address part gives address of starting cell and ending cell in destination
sheet, using sheet name in a1. 4 is used to give relative reference so that
we can drag down.
Indirect converts this text address to real reference address.
counta needs two address of start and end of range which is produced as
explained above.


"Rasoul Khoshravan" wrote in message
...
I have 12 worksheets with different names (I wish, I didn't change names
from Sheet[numbers] *)
I want to perform counta command on column A of each sheet in my reference
sheet.
With a Macro (Given by Gord Dibben) I was able to produce a list of sheet
names in my reference sheet.
Now I dont know how to address these names in counta command. On each
sheet it will be simply: counta(A:A)

Also is there any resources in Internet explaining about dealing with
calculation in different sheets.

*) I think if I leave name of sheets unchanged (Sheet[numbers]) then it
will be easy to use sheet names in commands. Isn't it so?

Leaving the sheets named as Sheet1, Sheet2, Sheet3, etc could make things a
little easier, but things work the same either way. The way to refer to
cells on another sheet in Excel looks like this (assume you're on Sheet2 and
want to refer to cells on Sheet1:
=Counta('Sheet1'!A:A)
purists will tell you that the ' marks aren't needed here, and they are not
in this case, but if your sheet name has a space in it, they are, so I showed
them. No harm in using them when they aren't required, but would be if they
were missing and the sheet name had a space in it, as:
=Counta('Last Quarterly Results'!A:A)

Hope this helps make things easier for you in the future.

"Rasoul Khoshravan" wrote:

I have 12 worksheets with different names (I wish, I didn't change names
from Sheet[numbers] *)
I want to perform counta command on column A of each sheet in my reference
sheet.
With a Macro (Given by Gord Dibben) I was able to produce a list of sheet
names in my reference sheet.
Now I dont know how to address these names in counta command. On each sheet
it will be simply: counta(A:A)

Also is there any resources in Internet explaining about dealing with
calculation in different sheets.

*) I think if I leave name of sheets unchanged (Sheet[numbers]) then it will
be easy to use sheet names in commands. Isn't it so?