Making code dynamic

Hi!

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

Please Login or Register  to view this content.

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?

Originally Posted by kvsrinivasamurthy

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A"&COUNTA($A$2:$A$100)+1&"'!Q:Q");C1))

The formula doesn't work, it shows error at =SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A"&COUNTA($A$2:$A$100)+1&"'!Q:Q");C1))

I guess the string within INDIRECT() should be "A2:A"&COUNTA($A$2:$A$100)+1&"'!Q:Q".
but I have no idea why the &"'!Q:Q" and ";C1" should be in the formula.
Usually Excel formula does not use the semi-colon such as ";C1".

Thank you, kvsrinivasamurthy, now the formula works.

However, the formula is still static due to COUNT($A$2:$A$100) part. As I wrote - I insert the formula using a macro code

Please Login or Register  to view this content.

The problem is that the part of the code, marked with red color is not dynamic. I'd like to have something like R[i]C1, where i is a number, e.g. the last row used in column A, instead of R100C1, for example

Please Login or Register  to view this content.

Unfortunately, when written like RiC1, the code doesn't work - it inserts wrong formula
=SUMPRODUCT(COUNTIF(INDIRECT("'"&OFFSET($A$2;0;0;COUNTA($A$2:'RiC1'))&"'!Q:Q");C4))

How can I make it work?

Originally Posted by jimsjoo

I guess the string within INDIRECT() should be "A2:A"&COUNTA($A$2:$A$100)+1&"'!Q:Q".
but I have no idea why the &"'!Q:Q" and ";C1" should be in the formula.
Usually Excel formula does not use the semi-colon such as ";C1".

The semicolon is because of my regional settings, you would probably need to use a colon.

The &"'!Q:Q" part and ";C1" part are used because the formula counts all occurrences of the text written in cell C1 on all sheets in the range Q: Q.

Originally Posted by frankt68

Hi!

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

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.
How should I change the code to accomplish that?

Do you mean when a record is added or removed from the list then you will run the code again (to adjust the last row)?

Originally Posted by Akuini

Do you mean when a record is added or removed from the list then you will run the code again (to adjust the last row)?

Yes, that is exactly what I mean.

Well, I found the solution that works for me. The code

Please Login or Register  to view this content.

inserts the formula

Formula:

Please Login or Register  to view this content.

and this works just fine. Even if a new record is added to the list, I don't need to re-run the macro, because the formula recalculates automatically.