Countif match

[joshbrfc]

Hi all,

I want to count the quantity of entries in a row on a specific tab in a workbook based on the match of someones name.

So this is the master data tab.

Then i have separate tabs.

And i want it to match with the name cell and the quantity of data entries.

Hope that makes sense.

Would Lookup be better?

So it updates automatically when another data entry is added.

Any help greatly appreciated. It's part of trying to improve right first time Want a tracker to focus on who needs training more and quick overview.

 

[Shloime]

this is the one I sent;
Function CountEntries(Employee_Name As String)
Dim WS As Worksheet
Dim Entries As Long
For Each WS In ThisWorkbook.Worksheets
If Not WS.Name = ActiveSheet.Name Then
If WS.Range("B3") = "Employee" Then
If WS.Range("F3") = Employee_Name Then
CountEntries = Application.WorksheetFunction.CountA(WS.Range(WS.Range("B20"), WS.Range("b20").End(xlDown)))
Exit Function
End If
End If
End If
Next
End Function

in your pic you have this lines in the wrong place
CountEntries = Application.WorksheetFunction.CountA(WS.Range(WS.Range("B20"), WS.Range("b20").End(xlDown)))
Exit Function

 

[joshbrfc]

this is the one I sent;
Function CountEntries(Employee_Name As String)
Dim WS As Worksheet
Dim Entries As Long
For Each WS In ThisWorkbook.Worksheets
If Not WS.Name = ActiveSheet.Name Then
If WS.Range("B3") = "Employee" Then
If WS.Range("F3") = Employee_Name Then
CountEntries = Application.WorksheetFunction.CountA(WS.Range(WS.Range("B20"), WS.Range("b20").End(xlDown)))
Exit Function
End If
End If
End If
Next
End Function

in your pic you have this lines in the wrong place
CountEntries = Application.WorksheetFunction.CountA(WS.Range(WS.Range("B20"), WS.Range("b20").End(xlDown)))
Exit Function

That's strange, it works now, it's just not automatically updating. I have to press enter on the name cell to update it.

Wondering if there's a way i can an update button.

Thanks for your replies, really really appreciate it.

 

[Joe4]

That's strange, it works now, it's just not automatically updating. I have to press enter on the name cell to update it.

Wondering if there's a way i can an update button.

Thanks for your replies, really really appreciate it.

User defined functions are NOT volatile, by default.
But you can add a line of code to make them so.
See here: Volatile Functions in Excel VBA

 

[joshbrfc]

User defined functions are NOT volatile, by default.
But you can add a line of code to make them so.
See here: Volatile Functions in Excel VBA

How would this work with module one i already have?

Would i enter as module 2? And would i change the formula in any of the cells or not?

 

[Joe4]

You just add it in the code of your function in VBA, as shown in the link I provided you:

Function CountEntries(Employee_Name as String)
    Application.Volatile
    ...

 

[joshbrfc]

You just add it in the code of your function in VBA, as shown in the link I provided you:

Function CountEntries(Employee_Name as String)
    Application.Volatile
    ...

Thanks for the reply again.

I have added.

It's still not working. I'm still having to press enter in the cell for it to update.

 

[Joe4]

How exactly is the data in your workbook being updated?
Manually?
Via links?

 

[joshbrfc]

How exactly is the data in your workbook being updated?
Manually?
Via links?

Manually.

So on the relevant tab below. So eg employee LF has their tab and it doesn't auto update until i go in the employee cell on master data tab and click in then press enter.

 

[Joe4]

The values in column B and being manually updated?
When/how is that happening?

Also, do you have automatic calculations enabled?

And what is the name of the module you have placed this User Defined Function in?

 

[joshbrfc]

The values in column B and being manually updated?
When/how is that happening?

Also, do you have automatic calculations enabled?

And what is the name of the module you have placed this User Defined Function in?

Sorry, results in column B are not being manually updated. They are updated after the individual tabs are updated.

So

LF's tab is updated manually as and when an error occurs.

And based off the entries in Column B on the respective tab.... 5 in this instance... this will populate the master data tab

See count entries formula... which thanks to @Shloime is working with

As you can see i have added your application.volatile line in, however unfortunately it's still not auto populating.

I am having to into master data and enter the employee cell and press enter for the count entries in column b to update.