B2 is a number eg. 0701....0702 for the GL period not a formula
--
Thanks
Rob


"vezerid" wrote:

The key, I believe is in this:

OR((Sheet1!$A$4:$A$1004=$B$2)*(Sheet1!$I$4:$I$1004 <$F$1),(Sheet1!$A
$4:$A$1004<$B$2)*(Sheet1!$I$4:$I$1004=""))

You are including a record if its A:A value is equal to B2 (what is in
B2?) and the paid date is less than F1 OR if its A:A value is less
than B2 and the paid date is blank. I don;t know the exact logic
behind this, but I can tell you there is a problem with using OR.

Logical functions do not work well in array formulas. You are partly
handling this by using multiplication but you still have trouble with
OR. If you want to use OR-functionality in an array formula, what
would be
OR(x,y)
where x and y are logical expressions returning T/F, now should become
(x+y)0
Hence, instead of your present OR construct:

(Sheet1!$A$4:$A$1004=$B$2)*(Sheet1!$I$4:$I$1004<$F $1)+(Sheet1!$A$4:$A
$1004<$B$2)*(Sheet1!$I$4:$I$1004="")0

The entire formula now being:

=IF(ISERR(SMALL(IF((Sheet1!$A$4:$A$1004=$B$2)*(She et1!$I$4:$I$1004<$F
$1)+(Sheet1!$A$4:$A$1004<$B$2)*(Sheet1!$I$4:$I
$1004="")0,ROW(INDIRECT("1:"&ROWS(Sheet1!A$4:A
$1004)))),ROWS($1:1))),"",INDEX(Sheet1!A$4:A$1004, SMALL(IF((Sheet1!$A
$4:$A$1004=$B$2)*(Sheet1!$I$4:$I$1004<$F$1)+(Sheet 1!$A$4:$A$1004<$B
$2)*(Sheet1!$I$4:$I$1004="")0,ROW(INDIRECT("1:"&R OWS(Sheet1!A$4:A
$1004)))),ROWS($1:1))))

If this still does not work maybe we need to reexamine the logic of
your formulas.

HTH
Kostis Vezerides

On Feb 28, 5:57 pm, robl wrote:
The or function does not return accruals no paid date. ( eg. blank date in
col I) FYI I do commit the formula with "cse"
--
Thanks
Rob

"vezerid" wrote:
On Feb 28, 4:23 pm, robl wrote:
I have an input sheet to track all monthly accruals for invoices. I want to
be able to list them on monthly sheets in the same workbook to track unpaid
invoices untill they are paid. Unpaid invoices need to re-accrue on the
following month (months) sheet. Here is the formula that has me stumped.

=IF(ISERR(SMALL(IF(OR((Sheet1!$A$4:$A$1004=$B$2)*( Sheet1!$I$4:$I$1004<$F$1),(Sheet1!$A$4:$A$1004<$B$ 2)*(Sheet1!$I$4:$I$1004="")),ROW(INDIRECT("1:"&ROW S(Sheet1!A$4:A$1004)))),ROWS($1:1))),"",INDEX(Shee t1!A$4:A$1004,SMALL(IF(OR((Sheet1!$A$4:$A$1004=$B$ 2)*(Sheet1!$I$4:$I$1004<$F$1),(Sheet1!$A$4:$A$1004 <$B$2)*(Sheet1!$I$4:$I$1004="")),ROW(INDIRECT("1:" &ROWS(Sheet1!A$4:A$1004)))),ROWS($1:1))))

This formula is in cell A4 on sheet 2 The range on sheet 1 is A4:A1004 and
I am listing the info from col A - col H in each row returned. Col I contains
the paid dete for each invoice. I am concantinating cols E & F into col E on
sheet 2 but that function is not in this formula.

What I have learned about these functions has come from trial and error
using posts from this community.

--
Thanks
Rob

And what exactly is the problem?