# Auto change sheet/tab name

#### BMD

##### Board Regular
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Bruce

Try the INDIRECT worksheet function.

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

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.

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))

Norie,

THANK YOU,
You are the best.

Bruce.

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.

Bruce

Where is the 500 located?

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.

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

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")

Replies
1
Views
79
Replies
5
Views
161
Replies
4
Views
172
Replies
4
Views
377
Replies
2
Views
250

1,203,502
Messages
6,055,772
Members
444,822
Latest member
Hombre

### 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.

### Which adblocker are you using?

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

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