Filling Named Ranges in Series

Hi,

I have a few columns with similar data which I have name as RNG1, RNG2 and so on.

I am using countif function in a different worksheet to look for and count certain values in these ranges.

So, my formula looks like =COUNTIF(Rng1, A2&"*") in cell A1

What I want to do is now drag this formula across so that cell A2 would be =COUNTIF(Rng2, A2&"*") and so on.

However, the name range value doesn't get filled in series as Rng1, Rng2 when you drag and copy the formula.

Any ideas to get around this?

Originally Posted by davegugg

You could potentially use the OFFSET function. You'd need to put this into A1, then copy over:

=COUNTIF(OFFSET(Rng1,0,COLUMN(A1)-COLUMN($A$1)), A2&"*")

Thank You Sir... this works