formula doesn't work in name

I have 2 names:

Please Login or Register  to view this content.

that work fine, but when I combine them into 1 name:

Please Login or Register  to view this content.

it doesn't work.

Why?

Doesn't work in what way?

When it's 2 names "=myCount" returns 9, but when it's 1 name "=myCount" returns 1.

I see what you mean. Looks like a bug to me. It will work if you use:
=COUNTA(INDEX(OFFSET(L!$C$7,2,0,ROWS(L!A:A)-ROW(L!$C$7)-1,1),0))

RS;
Yeah, but that's what I'm trying to get away from. Offset() is volatile, Index() isn't.
Teylyn and I have been working to try and get rid of Offset(), but haven't been successful. Oh well.

I don't understand - your original version contains OFFSET and nowhere have you mentioned trying to remove that.

RS;
Sorry got 2 different threads confused. My last message doesn't apply to your answer. I'll try your suggestion.

RS;
It worked fine. Thanks

Glad to help.

Now please comply with your signature, and mark the thread solved.

That makes twice that I forgot to mark a thread solved. I must be losing it.

It looks like the problem is being caused by the ROW() function, combined with the context of the formula (being in a name, plus the other functions in the name). If you test the formula using a constant (say 7), instead of ROW(L!$C$7) then it returns the correct result.


In the formula with the COUNTA, ROW(L!$C$7) returns an array type (ie. {7}).

Assuming the worksheet has 65536 rows, ROWS(L!B:B)-ROW(L!$C$7)-1 returns {65528}.


Now, if I do a little testing in VBA:

v1 and v2 are identical, the array type Height argument makes no difference.
But v3 and v4 are different -

Please Login or Register  to view this content.

Colin;
Now the question is: is that a bug or by design? Is there any reason to expect Row($C$7) to return an array when combined with CountA()? Especially since it works as expected when the formula is put in a cell.

Hi,

I'll just clarify one point:
COUNTA() itself isn't causing ROW(C7) to return an array type. It's the name context; when the formula is entered straight into a cell, =ROW(C7) returns 7, not {7}.

If you look at v1 and v2 in my VBA test, you can see that this "feature" doesn't cause a problem. But when you introduce COUNTA(), an aggregator function, it does cause a problem.

I'm honestly not certain why it's doing it, and I'd rather not speculate because I'll probably be wrong!



Although the analysis doesn't give us the ultimate inner nuts and bolts, it does give us enough understanding to suggest other workarounds. We know that the array type is causing the problem, so if we convert the array type to a double like this: {65528} ----> 65528 then we know that the formula will be okay. How to convert {65525} to 65528? Well, SUM() will do it.

examples -

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Not a solution that would have been immediately obvious, but is quite neat.