Dynamic range formule

    Hi,


    I've a little problem with my formule in Excel. I 've checked the explanation of dynamic ranges on the site here but I can't seem to find a solution myself.


    I have a little table with data that will be filled in and change a lot. Now I want a table next to it with a result of a filter that also can change. All this has to be done with formules, I can solve this problem with VBa but I would like to learn how I can solve this with formules.


    I've uploaded a small example that shows my problem and the desired outcome.


    Thanks for the help and support in advance.


    Gr,


    Gollem

    Re: Dynamic range formule


    Thanks for the reply Bob. :smile:


    It seems to work great, but can you explain a little bit what the formule does, how it works. I'm not figuring it out :confused:


    Thanks

    Re: Dynamic range formule


    Hi,


    Or..


    In F2 and copied down,


    =IF(C2=$J$1,$J$1&"#"&COUNTIF($C$2:C2,$J$1),"")


    In K2 and copied down and across,


    =IF(ROWS($K$2:$K2)<=COUNTIF($F$2:$F$17,"?*"),INDEX(B$2:B$17,MATCH($J$1&"#"&ROWS($K$2:$K2),$F$2:$F$17,0)),"")


    HTH

    Re: Dynamic range formule


    Quote from gollem

    Thanks for the reply Bob. :smile:


    It seems to work great, but can you explain a little bit what the formule does, how it works. I'm not figuring it out :confused:


    Thanks


    Basically, it just compares each item in column C for a 1 and extracts a column if that condition is true.

    HTH


    Bob

Participate now!

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

Register Yourself Login