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.

 

[BMD]

Dinner's on me I owe you!

 

[BMD]

This one is not adding up the stuff right

=SUMPRODUCT(INDIRECT("'"&C1&"'!G$3:G$"&C8)="Pass")*OR(("'"&C1&"'!I$3:I$"&C8)="Major")*(("'"&C1&"'!I$3:I$"&C8)="Minor")

AND/OR

=SUMPRODUCT(INDIRECT("'"&C1&"'!G$3:G$"&C8)="Pass")*OR(("'"&C1&"'!I$3:I$"&C8)="Major",("'"&C1&"'!I$3:I$"&C8)="Minor")

If the test pass and has a major or minor add them all up

Thanks,
Bruce.