Auto change sheet/tab name

BMD

Board Regular
Joined
Oct 5, 2005
Messages
211
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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Bruce

Try the INDIRECT worksheet function.

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

BMD

Board Regular
Joined
Oct 5, 2005
Messages
211
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 5, 2005
Messages
211

ADVERTISEMENT

Norie,

THANK YOU,
You are the best.

Bruce.
 

BMD

Board Regular
Joined
Oct 5, 2005
Messages
211
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Bruce

Where is the 500 located?
 

BMD

Board Regular
Joined
Oct 5, 2005
Messages
211
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,118,056
Messages
5,569,951
Members
412,299
Latest member
agentless
Top