Richard

Given that codes will be repeated in your table as you move down column B, I
do not think what you want is what you asked for. I may be wrong :-)

Instead, here is a suggestion.....

I have 5 columns and in cells A1 to E1, I have the following column
headings...
Date
Code
Issues
Returns
Net

I named the range from A2 downwards "da", the range B2 downwards "cd", the
range C2 downwards "is", the range D2 downwards "re" and the range E2
downwards "ne".

Assuming yout Returns column (columnd D) has negative numbers, enter the
following formula in cell E2 and drag it down as far as necessary.
=SUMPRODUCT((da<=A2)*(cd=B2)*(is+re))

In cells H2 downwards, I placed a unique list of all codes.
Enter this "array" formula in cell I2 and then drag down alongside the code
names in column H.
{=IF(MIN(IF(cd=H2,IF(ne<0,da)))=0,"",MIN(IF(cd=H2, IF(ne<0,da))))}


--
XL2002
Regards

William



"Richard Buttrey" wrote in
message ...
| William,
|
| Many thanks indeed for this solution, and indeed for the extremely
| swift response. This ng must be one of the best on the web. (I think
| there was a typo with the "D1:P1" bit in the formula, I guess you
| meant D2:P2 ).
|
| If I might be so bold as to pose a supplementary Q., any suggestions
| as to how I might refine this to work out the exact date a stock goes
| negative, from the stock issues/returns table?
| I guess one solutions is to sort that table by date, and have a new
| running cumulative balance column and then use a variation of the
| formula you've suggested.
|
| Kind regards,
|
|
|
|
|
| On Wed, 11 Aug 2004 11:24:58 +0100, "William"
| wrote:
|
| RBHi
| RB
| RBWith your dates in cells D2:P2 and your week ending stock values in
| D4:P4,
| RBthis array formula will return the earliest date of a negative
| stock value.
| RB{=IF(MIN(D4:P4)0,"N/A",MIN(IF(D4:P4<0,D1:P1)))}
|
| __
| Richard Buttrey
| Grappenhall, Cheshire, UK
| __________________________