Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf function refering to another worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf function refering to another worksheet
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 --------------- Jacob Skaria "SM" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf function refering to another worksheet
Thank you! I had a feeling it had to do with the quotes, but I didn't try
the correct combination you have below. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "SM" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refering cell to the worksheet name | Excel Worksheet Functions | |||
Function refering sheet index. | Excel Worksheet Functions | |||
Countif worksheet function frustrations | Excel Worksheet Functions | |||
refering to a named range on a different worksheet | Excel Programming | |||
Worksheet Function COUNTIF | Excel Programming |