Auto change sheet/tab name

[BMD]

I have =COUNTA(Account!$A$3:$A$176)
the reference it Account is about 16 times and I will need to change each one to make a tab for 'Account 145' 'Account 589' and so on. Is there a way to say

=COUNTA('H1'!$A$3:$A$176) and H1 is 'Account 589' and have the formula use and sheet name typed in H1?

I could then cut and paste the formula and only need to change the name in H1.

Thanks,
Bruce.

 

[Norie]

Bruce

Try the INDIRECT worksheet function.

=COUNTA(INDIRECT("'" & H1 & "'!$A$3:$A$176"))

 

[BMD]

WOW
Can I also do

=COUNTA(INDIRECT("'"&C1&"'!$B$3:$B$'&C6&'"))

C6 = 500 but as the sheet grows I can just change C6 and not have to change 500 to 600 in 46 places?

THANKS,
Bruce.

 

[Norie]

Bruce

You can do what you want, but I don't quite think you have the correct syntax.

=COUNTA(INDIRECT("'"&C1&"'!$B$3:$B$" &C6))

 

[BMD]

Norie,

THANK YOU,
You are the best.

Bruce.

 

[BMD]

I'm sure I can stump you if I try hard ..............

=COUNTIF(Account!B$3:$B$500,"N*")
=SUMPRODUCT((Account!G$3:G$500="Pass")*(Account!I$3:I$500="Major"))

How can I do the two formulas above and only the 500
something like
=COUNTIF(Account!B$3:$B$'&C8',"N*")

Try This,
Bruce.

 

[Norie]

Bruce

Where is the 500 located?

 

[BMD]

In C8 or the column of that formula

I would like the do both the sheet/tab name and the number of rows to count from that sheet/tab.

 

[BMD]

Don't tell me it can't be done?

 

[Norie]

Perhaps

=COUNTIF(INDIRECT("Account!B$3:$B$"&C8),"N*")

and

=SUMPRODUCT(INDIRECT("Account!G$3:G$"&C8)="Pass")*(INDIRECT("Account!G$3:G$"&C8)="Major")