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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Bruce

Try the INDIRECT worksheet function.

=COUNTA(INDIRECT("'" & H1 & "'!$A$3:$A$176"))
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
Bruce

Where is the 500 located?
 
Upvote 0
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.
 
Upvote 0
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")
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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
Back
Top