I'm creating a scorecard for employees based off of the tickets that they are working. I'm breaking the tickets worked by each employee into separate worksheets. The names of the worksheets will equal the name in Column A. On my main worksheet I'm calling SCORECARD I want to use COUNTIFS to go count the tickets that were worked that month by the person listed in Column A, but I can't figure out how to reference the worksheet name based off the name in Column A. So instead of typing this "Sheets("John Smith").Select" I want it to substitute "John Smith" with what ever value is in A (starting at A3). Likewise, instead of using this syntax for the countifs "=COUNTIFS('John Smith'!$B:$B,A2)", I want it to substitute "John Smith with what ever value is in A (starting at A3).
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Select Sheet & Countifs based on name in Column A
- Thread starter jconkl02
- Start date
Eric W
MrExcel MVP
- Joined
- Aug 18, 2015
- Messages
- 13,009
For worksheet formulas, use INDIRECT:
=COUNTIFS(INDIRECT("'"&A3&"'!$B:$B"),A2)
For VBA:
Sheets(Range("A3")).Select
although you usually don't need to select sheets in VBA, and it tends to slow down the macro. But that's the basic idea.
=COUNTIFS(INDIRECT("'"&A3&"'!$B:$B"),A2)
For VBA:
Sheets(Range("A3")).Select
although you usually don't need to select sheets in VBA, and it tends to slow down the macro. But that's the basic idea.
Upvote
0
Solution
Thank you Eric W. This works. I know I need to get out of the bad habit of using .select. I also use With Sheets/End With. Thanks for the assist.
Upvote
0
