How to dynamically link sheet name to count data from that sheet-CodePudding

Currently, I have a summary excel tab in my workbook where I am hardcoding the sheetname to get a count from it as follows:

=COUNTIF('SheetName1'!$D:$D,E$2)

I have the sheet name in the adjacent cell as well.

I have been trying to figure out how to dynamically link it with the adjacent cell value:

Something like =COUNTIF('&B3&'!$D:$D,E$2)

Edit: Easier test sample is COUNTA instead of COUNTIF: =COUNTA('SheetName1'!$A:$A)-1

The problem is the same

CodePudding user response：

You need INDIRECT() function-

=COUNTIF(INDIRECT(B3&"!D:D"),E2)

For above formula B3 contains sheetname and E2 contains criteria data.