How to use a cell text in a formula to select a different sheet?

Question

I have read through a few of these on here and they are saying to use indirect():

• Refer to Sheet with value in Cell?
• Using a cell text as sheet reference

I am not having any luck.

=COUNTIF(JAN!$C$4:$C$102,B5) 

This formula works but when I try to get D2 in there for JAN or FEB, I can't get it to work.

=COUNTIF(INDIRECT('D2' & "!" & $C$4:$C$102),B5)

D2 is a drop down data validation select from items to select JAN.,

How do I get D2 to equal JAN in the formula so it works with selecting the drop down selection?

Answer 1

I eventually used:

=COUNTIF(indirect(D2&"!$C$4:$C$102"),B5)