Problem with OFFSET function used in Named Ranges

**acsishere** · 11-12-2008, 10:30 AM

Dear Friends,

I am facing a problem with OFFSET function. The following formula is entered to define a named range (CNTENGLISH).

Please Login or Register  to view this content.

And this defined name is used in formulas to do some calculations. Of course it is working nicely.

But, when the few rows are deleted in the referred sheet, then the result in other sheets shows #NA error. Actually what happens with the named range is that it is not selecting the values properly.
I don't understand why? & what is the problem?

Any help? Thanks in advance.

acishere.

**NBVC** · 11-12-2008, 10:37 AM

Posting a sample workbook showing the problem might help us to figure it out.

**rwgrietveld** · 11-12-2008, 10:40 AM

The function indeed works fine ON IT'S OWN. How is it used? Have you used the formula evaluation. This is a nice tool to figure these things out.
I would expect a #REF or a #VALUE, but not a #NA. Is it a lookup function where the value itself is not found?

**acsishere** · 11-12-2008, 11:20 AM

As you said, I am posting the sample workbook. In this many ranged names may not function properly becuase it refers to some other workbook.

But, to explain the problem, I mentioned two ranged names CNTCSC, CNTSC and I have explained the problem in detail in the sample workbook.

Please give me the solution.

Thanks in advance.

acsishere.

**NBVC** · 11-12-2008, 11:35 AM

I think it has to do with the resultant array sizes...

Sumproduct must have same size ranges and each of those defined ranges yields a different size.

You should refer to same column for all your Offset formulas

If I change the CNTCLASS defined range to:

=OFFSET('3TO5'!$O$4,0,0,COUNTA('3TO5'!$O:$O)-1,1)

then I get results...

**acsishere** · 11-12-2008, 12:05 PM

Thanks for your reply.

I think I have also maintained the similarity with OFFSET functions when used to define named ranges.

Again I have attached the workbook in which I have explained my views on the same.

If we choose name manager window and see the CNTCLASS, then it chooses till the end of the row.

But, CNTCSC, CNTSC names are choosing upto #300 and remaining rows are left. And, I think that may be the reason for #NA error.

I seek your expertise in this regard.

Thanks in advance, acsishere.

**NBVC** · 11-12-2008, 12:18 PM

The COUNTA() parts in those formulas yield different results, since you have differing number of occupied cells.

Therefore, this will yield different array sizes in the Sumproduct() formula and so will cause Errors.

You should be consistent and refer to the same range in all the COUNTA() formulas so that the array size is consistent.

**acsishere** · 11-12-2008, 12:50 PM

Again, thanks for your continuous support.

As I am a beginner, I really do not understand what you said. But, I knew that I am wrong somewhere.

Sir, it is a request, can you please send me the explanations in my sample workbook?

Please Sir.

Thanks in advance,
acsishere.

**NBVC** · 11-12-2008, 02:11 PM

See attached.

Have a look at how I redefined the 3 named ranges...

Also, the results are not the same as you suggest because there only appears to be AB's in the same ranges as 3B.