Replace a row number with a dynamic cell value

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    In C1 I have VBA populate active row number upon each selection change.
    In G2 I have formula below, while trying to have the row number in column H replaced with value of C1 (the H&C1 bit).
    H5 = Sum for column H (I have totals on top). H6 where data $$ starts.
    This is to have G2 show remaining sum from total based on active row number in H.


    =SUM($H$5-(SUM($H$6:H&C1)))


    How ludicrous is that.

    Hello,


    You can test following :


    Code
    =SUM($H$5-(SUM(Indirect("H6:H"&C1))))


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    I came across a video last night about the Indirect function, and thought that would be exactly the way to address this matter - and it is - Thank you!

    Glad to hear it is all sorted out ...


    Thanks for your thanks ..And for the Like ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login