Get count of text of cell in several sheets in excel using VLOOKUP

Question

I have a workbook with several sheets.

In the range from B20:B25 I have some values from a dropdown list. In the left column when that values are wrote, the workbook automatically has to set the many times of that value has selected in all sheets simultaneously.

For example if in the sheet 2 I write the text "TEXT" in cell B22, the workbook has to look up in the last sheets if the text "TEXT" exists ONLY at range B20:B25. If this text was found, so the workbook has to add 1 to the left cell of value "TEXT".

The workbook can have to 60 sheets. And for example the "TEXT" could be wrote at first time in the sheet 55, and its left number must be 1. If the "TEXT" is write again in the sheet 56, the value at its left must be 2.

So the column A must be a counter of times that value in column B is wrote.

I tried with CELL, ADDRESS and MATCH but I had not lucky.

Does exists in VBA a formula that I can create something to count such this?

Answer 1

This might not be the most elegant way to do this but it seems to work:

Option Explicit

Function CountThroughSheet(R1 As Range, R2 As Range)
    Application.Volatile
    CountThroughSheet = 0
    Dim TrgAdd As String, i As Integer, R3 As Range
    TrgAdd = R1.Address
    For i = 1 To Application.Caller.Worksheet.Index
        For Each R3 In Sheets(i).Range(TrgAdd)
            If R3 = R2 Then CountThroughSheet = CountThroughSheet + 1
        Next R3
    Next i
End Function

To be placed in a module. Function is used as follow:

Note that where ever you place the formula, the first argument should be as large as the largest range in previous sheet index. Meaning that if your list decrease in size, you'll need to take blank rows to match previous lists positions.