Hi!

I use the below code to enter a formula in a cell


ActiveSheet.Range("D1").Select
ActiveCell.Formula2R1C1 = _
        "=SUMPRODUCT(COUNTIF(INDIRECT(""'""&R[1]C[-3]:R[22]C[-3]&""'!Q:Q""),RC[-1]))"
The code works and it enters the right formula =SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A23&"'!Q:Q");C1)) in cell D1 and depends on a list entered in column A.

I'd like to make the formula dynamic, so when a record is added or removed from the list, this would reflect in the formula also. For example, if there are 10 records in the list, the formula should be =SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A10&"'!Q:Q");C1)).

How should I change the code to accomplish that?