CountIf function refering to another worksheet

[SM]

Hi,

I've been fighting with this problem for a while, and tried using examples
from other posts, but I seem to be missing something here!

I have a table set up on Sheet1. The headings contain the Names of the
other worksheets in the workbook (e.g. Sheet2, Sheet3 etc...). The rows
contain peoples' names (e.g. Smith, John etc...). Within each table element
I'm trying to insert a CountIf result that determines how often the name
appears in column S of a given worksheet such as Sheet2.

Here is some sample code I've tried with no success - there are many other
permutations I'll omit.

Cells(j, i) = "=COUNTIF('" & Cells(1, i) & "'!S:S, " & Cells(j, 1) & ")"
where
j = row of the table on Sheet1
i = column of the table on Sheet1
cells(1, i) = name of the worksheet containing the information to be
counted
cells(j,1) = name of the person that is the CountIf argument

Any suggestions to get this to work would be greatly appreaciated!

SM

 

[Jacob Skaria]

Try the below. You missed the double quotes

Cells(j, i) = "=COUNTIF('" & Cells(1, i) & "'!S:S, """ & Cells(j, 1) & """)"

If this post helps click Yes

 

[SM]

Thank you! I had a feeling it had to do with the quotes, but I didn't try
the correct combination you have below.