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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
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,344
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,344
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,344
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")
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,994
Messages
5,834,775
Members
430,321
Latest member
yemisimi11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top