Hi all

I have the following formula

=IF(ISNA(VLOOKUP(I14,Paid!A:B,2,FALSE)),IF($K14<1, 5,3),(VLOOKUP(I14,Paid!A:B,2,FALSE)))

This is used to check if invoice no is on paid sheet then enter 1, if not
then depending on age of invoice (column K) then either 5 or 3.

I need to add another if function - =if(left(T14,8)"Promised",2 - this is to
show payments due -
=if(left(T14,8)"Promised",2,IF(ISNA(VLOOKUP(I14,Pa id!A:B,2,FALSE)),IF($K14<1,5,3),(VLOOKUP(I14,Paid! A:B,2,FALSE))))

Conflicts when invoice is paid and invoice No appears on paid sheet - still
remains as 2.

Order of checking should be: - check if paid if yes then 1 - if no then
check if promised, if yes then 2, if no then check age, 5 if <1 3 if1.

Any ideas? or if not best explained please let me know

Peter,

Try this:

=IF(NOT(ISNA(VLOOKUP(I14,Paid!A:B,2,FALSE))),VLOOK UP(I14,Paid!A:B,2,FALSE),IF(LEFT(T14,8)="Promised" ,2,IF($K14<1,5,3)))

HTH,
Bernie
MS Excel MVP


"Peter" wrote in message
...
Hi all

I have the following formula

=IF(ISNA(VLOOKUP(I14,Paid!A:B,2,FALSE)),IF($K14<1, 5,3),(VLOOKUP(I14,Paid!A:B,2,FALSE)))

This is used to check if invoice no is on paid sheet then enter 1, if not
then depending on age of invoice (column K) then either 5 or 3.

I need to add another if function - =if(left(T14,8)"Promised",2 - this is
to
show payments due -
=if(left(T14,8)"Promised",2,IF(ISNA(VLOOKUP(I14,Pa id!A:B,2,FALSE)),IF($K14<1,5,3),(VLOOKUP(I14,Paid! A:B,2,FALSE))))

Conflicts when invoice is paid and invoice No appears on paid sheet -
still
remains as 2.

Order of checking should be: - check if paid if yes then 1 - if no then
check if promised, if yes then 2, if no then check age, 5 if <1 3 if1.

Any ideas? or if not best explained please let me know

=IF(ISNA(VLOOKUP(I14,Paid!A:B,2,FALSE)),IF(LEFT(T1 4,8)="Promised",2,IF($K14<1,5,3)),(VLOOKUP(I14,Pai d!A:B,2,FALSE)))

Got it!! Just had to re-arrange the brackets...always helps :)

"Peter" wrote:

Hi all

I have the following formula

=IF(ISNA(VLOOKUP(I14,Paid!A:B,2,FALSE)),IF($K14<1, 5,3),(VLOOKUP(I14,Paid!A:B,2,FALSE)))

This is used to check if invoice no is on paid sheet then enter 1, if not
then depending on age of invoice (column K) then either 5 or 3.

I need to add another if function - =if(left(T14,8)"Promised",2 - this is to
show payments due -
=if(left(T14,8)"Promised",2,IF(ISNA(VLOOKUP(I14,Pa id!A:B,2,FALSE)),IF($K14<1,5,3),(VLOOKUP(I14,Paid! A:B,2,FALSE))))

Conflicts when invoice is paid and invoice No appears on paid sheet - still
remains as 2.

Order of checking should be: - check if paid if yes then 1 - if no then
check if promised, if yes then 2, if no then check age, 5 if <1 3 if1.

Any ideas? or if not best explained please let me know

Hi Bernie

Thank you for taking the time to help me on this one - but somehow I managed
it (just a matter of re-arranging the brackets...)

=IF(ISNA(VLOOKUP(I14,Paid!A:B,2,FALSE)),IF(LEFT(T1 4,8)="Promised",2,IF($K14<1,5,3)),(VLOOKUP(I14,Pai d!A:B,2,FALSE)))

"Bernie Deitrick" wrote:

Peter,

Try this:

=IF(NOT(ISNA(VLOOKUP(I14,Paid!A:B,2,FALSE))),VLOOK UP(I14,Paid!A:B,2,FALSE),IF(LEFT(T14,8)="Promised" ,2,IF($K14<1,5,3)))

HTH,
Bernie
MS Excel MVP


"Peter" wrote in message
...
Hi all

I have the following formula

=IF(ISNA(VLOOKUP(I14,Paid!A:B,2,FALSE)),IF($K14<1, 5,3),(VLOOKUP(I14,Paid!A:B,2,FALSE)))

This is used to check if invoice no is on paid sheet then enter 1, if not
then depending on age of invoice (column K) then either 5 or 3.

I need to add another if function - =if(left(T14,8)"Promised",2 - this is
to
show payments due -
=if(left(T14,8)"Promised",2,IF(ISNA(VLOOKUP(I14,Pa id!A:B,2,FALSE)),IF($K14<1,5,3),(VLOOKUP(I14,Paid! A:B,2,FALSE))))

Conflicts when invoice is paid and invoice No appears on paid sheet -
still
remains as 2.

Order of checking should be: - check if paid if yes then 1 - if no then
check if promised, if yes then 2, if no then check age, 5 if <1 3 if1.

Any ideas? or if not best explained please let me know