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

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?

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?

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?

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?

robl wrote:
B2 is a number eg. 0701....0702 for the GL period not a formula

rob1

Been my experience that "0701" should not be a number, but text, unless
you want to perform math on it...

Just my 2cents

Beege

The logic is.... if A:A value = B2 and the paid date is less than F1 (F1 =
end of current GL period) then these invoices are accrued in the current
period. OR.... if A:A value < B2 and the paid date is blank then these
invoices are re-accrued in the current period.
--
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?

OK, your logic is correctly referred to in the formula (provided both
B2 and the values in A:A are of the same data type, so that you don't
get a bug on the equality).

Thus, I believe you must consider the equivalent that I suggested
instead of OR in teh array formula.

HTH
Kostis

On Feb 28, 7:21 pm, robl wrote:
The logic is.... if A:A value = B2 and the paid date is less than F1 (F1 =
end of current GL period) then these invoices are accrued in the current
period. OR.... if A:A value < B2 and the paid date is blank then these
invoices are re-accrued in the current period.
--
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?

Beege
You are right it is text because of leading zero.
--
Thanks
Rob


"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