Conditional formatting formula issues (dynamically changing the sheet name in the formula)

Hello all,
I have a workbook that has roughly 100 sheets in it, the sheet names are T1-T49 and V1-V47, I have a “summary” sheet that collects the status of various work that is being done in those sheets. On the summary page, in Column B I have the list of the sheet names. We will use row 4 for this example, I want to use a formula to change the fill color of cell D4 based on text that is on Sheet T1 Cell B13, “In progress” for example. I have this working as a conditional formatting rule with the following formula, =‘T2'!$B$13="In Progress" however the sheet name needs to be edited for each conditional rule. I was looking at using indirect to look up the sheet name so i can copy the formula and not have to manually edit the formula when its copied to a new row. Does this make sense? Ideally I would like something like this =INDIRECT("'" & B4 & "'!$B$13="In Progress”) would turn the cell to be yellow so, when I copy the formatting it updates the B4 to be B5.

If you want to see the spreadsheet I can share it, so you can understand what I am trying to do. I’m just trying to avoid having to manually edit 2500 conditional formatting rules.

For Row 4

The conditional formatting rules I have working in E4 on summary are:

=‘T1'!$B$13="Not Applicable" changes the cell to be gray
=‘T1'!$B$13="Not Started"”changes the cell to be red
=‘T1'!$B$13="In Progress" changes the cell to be yellow
=‘T1'!$B$13="Completed" changes the cell to be green

Then in F4 on summary:

=‘T1'!$B$14="Not Applicable" changes the cell to be gray
=‘T1'!$B$14="Not Started" changes the cell to be red
=‘T1'!$B$14="In Progress" changes the cell to be yellow
=‘T1'!$B$14="Completed" changes the cell to be green

In row 5 on summary:

E5 on summary are:

=‘T2’!$B$13="Not Applicable" changes the cell to be gray
=‘T2’!$B$13="Not Started"”changes the cell to be red
=‘T2’!$B$13="In Progress" changes the cell to be yellow
=‘T2’!$B$13="Completed" changes the cell to be green

Then in F5 on summary:

=‘T2’!$B$14="Not Applicable" changes the cell to be gray
=‘T2’!$B$14="Not Started" changes the cell to be red
=‘T2’!$B$14="In Progress" changes the cell to be yellow
=‘T2’!$B$14="Completed" changes the cell to be green

And those are working great, they just require the sheet name to be modified when the formatting is copied to a new cell.

Or, is there a better way to create a stop light chart on the summary sheet based on text in the cells on other sheets?

Thanks for any help.

Wanted to add, I got Excel to the this formula:

=INDIRECT("'" & B4 & "'!$B$13=“In Progress") in the conditional format rules, but the colors aren't changing, in fact they have stopped working.