Dynamically Change Table Name in a Structured Reference Using Index+Match??

You would probably get more people to help you if you attached a sample file (please read the yellow banner at the top of the page).
Based on what you're saying you want to do, I'm not sure you'll be able to get away from using Indirect, but let's see.

Attachment's added!

I'm not sure I understand. I want to make sure I know why you don't like the formula you have. Is it because the table header of "Referral" might change to something else? Will it always be in the second column? Can you reference the table header with Indirect? I'm also not sure what you mean by "Excel does not auto-populate".

If you can reference the column instead of the table header, and you want to reference the sheet name from the date (instead of the table name), you could try this in cell D7:
=COUNTIFS(INDIRECT("'"&TEXT(D$4,"mm.dd.yy")&"'!$B:$B"),"Yes",INDIRECT("'"&TEXT(D$4,"mm.dd.yy")&"'!$A:$A"), $A7)
(Your sheet names need to be consistent though for this. Your example sheet are different format - one has a 2 digit year, and the other has a 4 digit year.)

Lastly, in your file you state:

*Problem with this Formula is that [Referral] and [Location] have to be typed instead of suggested by Excel and are not Dynamic

How would they be "suggested" by Excel?

worksheet name : Mthly

Cell D3 formula , Drag right

HTML Code: 

There are Two ways

Attachment only do the first way

Use date format as worksheet name, but it must be in standard mode, for example 12.31.2020 , 01.31.2021 and etc

worksheet name : Mthly

Cell D7 formula , Drag down and across

HTML Code: 

Another way is to change the worksheet name to this BegBal,JanBal,FebBal and etc

worksheet name : Mthly

Cell D7 formula , Drag down and across

HTML Code: