# 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

## Files

• Re: Dynamic range formule

Try this

## Files

HTH

Bob

• 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

Thanks kris, seems to work also nice. : D

I'll take a look at it.

• 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!