bookkeeping-payment confirmation

[puiuluipui]

Hi, i have this formula that show me how many days i have until next pay
{=IF(COUNT(sheet1!A1:G1),IF(TODAY()>=MAX(sheet1!A1:G1),"Expired",INDEX(sheet1!A1:G1,,MIN(IF(sheet1!A1:G1>TODAY(),COLUMN(sheet1!A1:G1)))-COLUMN(sheet1!A1:G1)+1)-TODAY()),"")}

In this range (A1:G1)i have dates. In every month i have to pay something.
i need to make a table in sheet2!A1:G1 in which i will write OK when the
rate is payed.

Ex: sheet1
A B C D
E F G
14.08.2009 14.09.2009 14.10.2009 14.11.2009 14.12.2009 14.01.2010 14.02.2010

sheet2:
A B C D
E F G
OK

Rate 1 is payed, and i write ok. The formula to look in range sheet2!A1:G1
and to see that in corresponding cell (A1) is something, so the formula to
work further. If sheet2!A1:G1 was empty, then the formula to display "please
pay" until i will write something in the corresponding cell.
Sheet1!A1:G1 represent dates when i need to pay
Sheet2!A1:G1 represent the confirmation that i have payed.

Can this be done?
Thanks!

 

[Bob Phillips]

Is this what you mean?

=IF(NOT(COUNTA(A1:G1)),"Please pay",
IF(COUNT(Sheet1!A1:G1),IF(TODAY()>=MAX(Sheet1!A1:G1),"Expired",
INDEX(Sheet1!A1:G1,,MIN(IF(Sheet1!A1:G1>TODAY(),COLUMN(Sheet1!A1:G1)))-COLUMN(Sheet1!A1:G1)+1)-TODAY()),""))

 

[puiuluipui]

Hi Bob, it's almost perfect, One problem though. I need to write in sheet2 an
ok for every date in sheet1!A1:G1. Now, if there is an ok in A1, then the
formula is not showing please pay when all the others date has past. If i
already have2 or more dates that has espired, and only one OK in A1, then the
"please pay" is not showing anymore.
I need to put an OK in A1 For the date in A1 sheet 1....an OK in B1 for the
date in B1 sheet 1.....An OK if rate from august is payed, an OK if rate from
september is payed...etc.
If the date in sheet1!A1 has past and in Sheet2!A1 is an OK, then the
formula to exclude previous dates(A1), and to look in range B1:G1.
I hope you can understand my english.
Thanks!

 

[Bob Phillips]

Is this better?

=IF(NOT(SUMPRODUCT(--(Sheet1!A1:G1>=TODAY()),--(Sheet2!A1:G1="OK"))),"Please
pay",
IF(COUNT(Sheet1!A1:G1),IF(TODAY()>=MAX(Sheet1!A1:G1),"Expired",
INDEX(Sheet1!A1:G1,,MIN(IF(Sheet1!A1:G1>TODAY(),COLUMN(Sheet1!A1:G1)))-COLUMN(Sheet1!A1:G1)+1)-TODAY()),""))

 

[puiuluipui]

Better? It's perfect! Just one more thing. Instead of "OK" i need to write
the date and bill's number (45216/01.09.2009).
Can this be done?
Thanks!

 

[Bob Phillips]

That formula doesn't output OK, that is manually entered. And I can see the
date, but where does the bills number come from?

 

[puiuluipui]

Hi Bob, i have rates to pay and i need to enter receipt's number and date.
(That number and date was an example). When i pay monthly rate, i get an
receipt and i need to enter every month, receipt's nr and date so i can have
a better management of my rates. And if i need to see in...i don't know,
let's say in july, the receipt number and date to confirm that this rate was
payed, to look in this table and see. It wold be easier than search in all
papers. I need this workbook to tell me when i have to pay and how many days
left until next rate, and in the same time to have an database and evidence
with receipts. Thats why i need to write in sheet2 july receipt
nr/date,....september receipt nr/date....

I would be very helpful for me.
Can this be done?
Thanks!

 

[puiuluipui]

Hi Bob, can this code work if in sheet 2 i manually enter numbers?
Thanks!