| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
bookkeeping-payment confirmation
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!A 1:G1TODAY(),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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
bookkeeping-payment confirmation
Is this what you mean?
=IF(NOT(COUNTA(A1:G1)),"Please pay", IF(COUNT(Sheet1!A1:G1),IF(TODAY()=MAX(Sheet1!A1:G 1),"Expired", INDEX(Sheet1!A1:G1,,MIN(IF(Sheet1!A1:G1TODAY(),CO LUMN(Sheet1!A1:G1)))-COLUMN(Sheet1!A1:G1)+1)-TODAY()),"")) -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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!A 1:G1TODAY(),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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
bookkeeping-payment confirmation
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" wrote: Is this what you mean? =IF(NOT(COUNTA(A1:G1)),"Please pay", IF(COUNT(Sheet1!A1:G1),IF(TODAY()=MAX(Sheet1!A1:G 1),"Expired", INDEX(Sheet1!A1:G1,,MIN(IF(Sheet1!A1:G1TODAY(),CO LUMN(Sheet1!A1:G1)))-COLUMN(Sheet1!A1:G1)+1)-TODAY()),"")) -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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!A 1:G1TODAY(),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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
bookkeeping-payment confirmation
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:G 1),"Expired", INDEX(Sheet1!A1:G1,,MIN(IF(Sheet1!A1:G1TODAY(),CO LUMN(Sheet1!A1:G1)))-COLUMN(Sheet1!A1:G1)+1)-TODAY()),"")) -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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" wrote: Is this what you mean? =IF(NOT(COUNTA(A1:G1)),"Please pay", IF(COUNT(Sheet1!A1:G1),IF(TODAY()=MAX(Sheet1!A1:G 1),"Expired", INDEX(Sheet1!A1:G1,,MIN(IF(Sheet1!A1:G1TODAY(),CO LUMN(Sheet1!A1:G1)))-COLUMN(Sheet1!A1:G1)+1)-TODAY()),"")) -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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!A 1:G1TODAY(),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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
bookkeeping-payment confirmation
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" wrote: 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:G 1),"Expired", INDEX(Sheet1!A1:G1,,MIN(IF(Sheet1!A1:G1TODAY(),CO LUMN(Sheet1!A1:G1)))-COLUMN(Sheet1!A1:G1)+1)-TODAY()),"")) -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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" wrote: Is this what you mean? =IF(NOT(COUNTA(A1:G1)),"Please pay", IF(COUNT(Sheet1!A1:G1),IF(TODAY()=MAX(Sheet1!A1:G 1),"Expired", INDEX(Sheet1!A1:G1,,MIN(IF(Sheet1!A1:G1TODAY(),CO LUMN(Sheet1!A1:G1)))-COLUMN(Sheet1!A1:G1)+1)-TODAY()),"")) -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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!A 1:G1TODAY(),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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
bookkeeping-payment confirmation
That formula doesn't output OK, that is manually entered. And I can see the
date, but where does the bills number come from? -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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" wrote: 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:G 1),"Expired", INDEX(Sheet1!A1:G1,,MIN(IF(Sheet1!A1:G1TODAY(),CO LUMN(Sheet1!A1:G1)))-COLUMN(Sheet1!A1:G1)+1)-TODAY()),"")) -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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" wrote: Is this what you mean? =IF(NOT(COUNTA(A1:G1)),"Please pay", IF(COUNT(Sheet1!A1:G1),IF(TODAY()=MAX(Sheet1!A1:G 1),"Expired", INDEX(Sheet1!A1:G1,,MIN(IF(Sheet1!A1:G1TODAY(),CO LUMN(Sheet1!A1:G1)))-COLUMN(Sheet1!A1:G1)+1)-TODAY()),"")) -- __________________________________ HTH Bob "puiuluipui" wrote in message ... 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!A 1:G1TODAY(),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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Starting payment when increase rate is known & total payment is kn | Excel Worksheet Functions | |||
| calculate payment with first payment due date variable? | Excel Worksheet Functions | |||
| where can I get a weekly bookkeeping template | Excel Discussion (Misc queries) | |||
| Personal accounting/bookkeeping in Excel | Excel Discussion (Misc queries) | |||
| how do you get a positive number payment with a mortgage payment . | Excel Worksheet Functions | |||
Hybrid Mode
