Use cell value to determine a range in part of formula

Question

I am building a counter in Google Sheets that counts how many times a name appears in a column from Sheet A:

Sheet A

	A
1	John
2	John
3	Ann
4	Mary

Sheet B

	A	B	C
1	Name	Counter	Desired Range of "Sheet A"
2	John	2	SheetA!A1:A4
3	Mary	1
4	Ann	1

The formula I used in Sheet B to count the names in column A of Sheet A is: =COUNTIF(SheetA!A1:A4,("*"&A2&"*"))

The "&A2&" is dynamic and gets the value (name) on each respective line of the cell on Sheet B.

I want to make the part SheetA!A1:A4 dynamic too.

Since I am using that same formula in each of the cells in column B of Sheet B, I would like that if I edit that one single cell (that states the range of a certain sheet), it would apply to all of the cells using that formula instead of editing each formula.

I am searching for something like this: =COUNTIF( C2 ,("*"&A2&"*")) where C2 is the range stated on the cell.

Is it possible?

Answer 1

Use:

=COUNTIF(INDIRECT(C2),"*"&A2&"*")