"Dallman Ross" <dman@localhost. wrote in message
...
In , T. Valko
spake thusly:

"Dallman Ross" <dman@localhost. wrote in message
...

Oh, a follow-up question is, I was playing with named ranges
trying to get a flexible designation for "lastRow" in these
formulas instead of 16 (or the safe 500 I used), but so far
with no luck. Just a minor touch I wouldn't mind making work,
but not important -- what we have so far works well. I tried
(in Names Manager):

colBottom
=OFFSET(MergeSheet!$A$1,lastRow-1,COLUMN()-1)

where "lastRow" is
=COUNTA(MergeSheet!$A:$A)

and then using, in the formula, e.g.,
SUMPRODUCT(--(B$2:colBottom=B2),...

But no-go.

Here's one way:

Assuming the data is on Sheet1 in the range B2:Hn and you have a header
row
in row 1, B1:H1.

Define the dynamic range as:

Name: rng (or whatever name you want)

Refers to: =OFFSET(Sheet1!$B$2:$H$2,,,COUNTA(Sheet1!$B:$B)-1)

Then the formula becomes (still an array):

=SUMPRODUCT(--(INDEX(rng,,1)=B2),--(INDEX(rng,,3)=D2),--(IF(B2="buy",H2<INDEX(rng,,7),H2INDEX(rng,,7))))+ 1

Whe
INDEX(rng,,1) refers to the 1st column of the named range = column B
INDEX(rng,,3) refers to the 3rd column of the named range = column D
INDEX(rng,,7) refers to the 7th column of the named range = column H

Hmm, it makes sense, and I tried it, but I can't get it to work. The
logic seems similar to my earlier attempts that also failed despite
my expectation that they would work. I wonder if the problem is that
I need to turn on "Accept labels in formulas" in the program options.
This is just a wild guess, but that option is not currently
checked. I use Excel 2002, for what it's worth.

Anyway, I now put the long "mess" into a "name" and refer to the
name to set the formula in VBA, and that works fine.

Note: In the formula you see that we are only testing for
"buy". I assumed that there would only be 2 possibilities: Buy or
Sell, and nothing else. So the logic is that if the cell doesn't
contain "Buy" then it *must* contain "Sell".

That is correct. I was able to follow your algorithm's logic, as well.
Thank you for checking.

How to recognize when an array condition exists....that's kind of hard to
explain. I'll say that experience is the best teacher.

See if this helps:

http://cpearson.com/excel/ArrayFormulas.aspx

I'll try to read it over in the next number of days. Thanks very much
again!

Dallman

I wonder if the problem is that I need to turn on "Accept labels in
formulas"

No, that's totally unrelated.

Another way is to create a separate dynamic range for each of the 3 ranges
needed. I thought just using a single dynamic range would be less confusing
(but it does make the formula slightly longer).

--
Biff
Microsoft Excel MVP