+ Reply to Thread
Results 1 to 11 of 11

Making code dynamic

  1. #1
    Registered User
    Join Date
    05-07-2013
    Location
    Celje, Slovenia
    MS-Off Ver
    2016, 365
    Posts
    29

    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?

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Making code dynamic

    Try this

    =SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A"&COUNTA($A$2:$A$100)+1&"'!Q:Q");C1))
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  3. #3
    Registered User
    Join Date
    05-07-2013
    Location
    Celje, Slovenia
    MS-Off Ver
    2016, 365
    Posts
    29

    Re: Making code dynamic

    Quote Originally Posted by kvsrinivasamurthy View Post

    =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))

  4. #4
    Registered User
    Join Date
    08-25-2021
    Location
    Seoul
    MS-Off Ver
    2016
    Posts
    3

    Re: Making code dynamic

    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".

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Making code dynamic

    This works

    =SUMPRODUCT(COUNTIF(INDIRECT("'"&OFFSET($A$2,0,0,COUNTA($A$2:$A$100))&"'!Q:Q"),C1))

    If required
    Replace , by ;
    for your setting.

  6. #6
    Registered User
    Join Date
    05-07-2013
    Location
    Celje, Slovenia
    MS-Off Ver
    2016, 365
    Posts
    29

    Re: Making code dynamic

    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?

  7. #7
    Registered User
    Join Date
    05-07-2013
    Location
    Celje, Slovenia
    MS-Off Ver
    2016, 365
    Posts
    29

    Re: Making code dynamic

    Quote Originally Posted by jimsjoo View Post
    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.

  8. #8
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: Making code dynamic

    Quote Originally Posted by frankt68 View Post
    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)?

  9. #9
    Registered User
    Join Date
    05-07-2013
    Location
    Celje, Slovenia
    MS-Off Ver
    2016, 365
    Posts
    29

    Re: Making code dynamic

    Quote Originally Posted by Akuini View Post
    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.

  10. #10
    Registered User
    Join Date
    05-07-2013
    Location
    Celje, Slovenia
    MS-Off Ver
    2016, 365
    Posts
    29

    Re: Making code dynamic

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

    Please Login or Register  to view this content.
    inserts the formula
    Formula: copy to clipboard
    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.

  11. #11
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Making code dynamic

    Ref Post #6
    It will not be static , because the count depends upon the filled cells in Column A. So it is dynamic.
    Don't replace $A$100 by RC. There is no necessity. If data is beyond A100 then change it To A200 or so. The intention is the data range should be inside the defined range in Counta Formula.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Making sheet name dynamic
    By Rahul15292000 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-13-2019, 11:40 PM
  2. Making Dynamic Name Range more dynamic
    By dluhut in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-23-2019, 11:51 AM
  3. Making a dynamic query Can any help me out.
    By Deandre305 in forum Excel General
    Replies: 0
    Last Post: 08-09-2016, 12:32 PM
  4. Making dynamic vlookup
    By Knarf6 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-02-2016, 10:30 AM
  5. help with making years dynamic
    By ammartino44 in forum Excel General
    Replies: 2
    Last Post: 09-12-2015, 05:51 PM
  6. making macro dynamic
    By rexer231 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-11-2013, 03:48 AM
  7. Making condition dynamic?
    By aposatsk in forum Excel General
    Replies: 3
    Last Post: 08-16-2006, 08:43 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1