and / or function
 

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 / or function
 

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?

and / or function
 

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?

and / or function
 

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?

and / or function
 

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?

and / or function
 

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

and / or function
 

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

and / or function
 

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?

and / or function
 

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?

and / or function
 

Vezrerid

I changed the OR function as soon as I got your post and the logic part is
still not working The invoices returned are mostly those with a blank paid
date I haven't figured out which part of the logic is not working. I think it
is related to the I:I< F1 maybe I don't need the date restraint on that part.
I'll let you know.
--
Thanks for all your help
Rob


"vezerid" wrote:

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?

and / or function
 

Vezerid

That finally fixed it. I deleted the date resriction because I really want
all current period accruals + old unpaid accruals. Now I have two small
questions....1. How can I have the formula show blank when there is no
result? 2. why did you put 0 the revised formula? I tried with them out
and I didn't see a difference.
--
Thanks
Rob


"vezerid" wrote:

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?

and / or function
 

Rob,
glad it worked (as much as it did...). In answering your questinos:
1. Your original formula was indeed leaving it blank when nothing was
found. This is the philosophy:

=IF(ISERROR(something),"",INDEX(something))

The _something_ was where we intervened. Part of the _something_ is
the conditions with AND/OR. Thus the formula tests if _something_
exists; if it does not it returns "". Is it not doing this now?

2. The idea of 0 is the following: We replace AND/OR with
Multiplication/Addition. Inside an IF's first argument, where Excel
expects a logical value, if we instead supply a numeric expression, it
will be converted to True/False as follows:
0 - False, Anything < 0 - True.

If a and b are logical expressions then the following two are
equivalent:
=IF(OR(a, b), xxx, yyy)
=IF(a+b, xxx, yyy)

In this context simple addition is enough b/c we don;t care if both
conditions are true and produce 2 as a result. It is <0, hence true.
Thus, in your context the 0 was not really necessary b/c the
operations were made in a logical context and all we cared about was
that it is nonzero anyway. The 0 is necessary when we have formulas
with the IF philosophy but without IF. Example:

A2 contains points collected. B2 contains victories in a tournament.
Bonus is INT(A2/100)+B2. But to get the bonus I must either have more
than 1000 points or more than 3 victories. Without an IF:

=(INT(A2/100)+B2)*((A21000)+(B23)0)

If I don't use the 0 then, if both conditions hold, I will get double
the bonus. The 0 basically equates all non-zero values to TRUE, which
is then converted to 1 when multiplied.

HTH
Kostis Vezerides
On Feb 28, 11:40 pm, robl wrote:
Vezerid

That finally fixed it. I deleted the date resriction because I really want
all current period accruals + old unpaid accruals. Now I have two small
questions....1. How can I have the formula show blank when there is no
result? 2. why did you put 0 the revised formula? I tried with them out
and I didn't see a difference.
--
Thanks
Rob

"vezerid" wrote:
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?