Accounts Payable - trying to get daily updates

[JohnFex]

Hello,

I am looking for some formula and structure advise on my Accounts Payable worksheet please.

1. It is very unscalable as every time I want to add a new item I have to manually change 3 formulas (K26,K27 AND B1). This is a fundamental flaw in my design, so any ideas are really welcome.

2. I cannot track if any payments have been missed, so I would like a formula in B2 to calculate up to todays date and see if there are any payments that should be made or show 0.

Many thanks in advance.


John

[JohnFex]

Sorry to anyone who had a quick look at it, I had attached the wrong Excel sheet. I have now attached the correct one - Payables.

[kvsrinivasamurthy]

In B1 use
=SUM(IF(MOD(ROW(INDIRECT("$E$8:$E"&MATCH("*",$A:$A,-1)+2))-ROW($E$8),3)=0,INDIRECT("$E$8:$E"&MATCH("*",$A:$A,-1)+2),""))
In K28 use
=SUM(IF(MOD(ROW(INDIRECT("$K$7:$K"&MATCH("*",$A:$A,-1)+2))-ROW($K$7),3)=0,INDIRECT("$K$7:$K"&MATCH("*",$A:$A,-1)+2),""))
IN K29 use
=SUM(IF(MOD(ROW(INDIRECT("$K$8:$K"&MATCH("*",$A:$A,-1)+2))-ROW($K$8),3)=0,INDIRECT("$K$8:$K"&MATCH("*",$A:$A,-1)+2),""))

All are array formulas(After pasting formula Press Ctrl+Shift+Enter)

[JohnFex]

Thank you for trying Kesav, I just added those formulas though and the one in B1 didn't produce the correct total and the K27 and K28 ones didn't do anything. Would you mind having another quick look.

Thanks,


John

[kvsrinivasamurthy]

I have edited formulas in my previous post so that rows can be inserted . All are array formulas. After pasting formula don't simply enter key , instead press Ctrl+Shift+Enter Keys together.

[JohnFex]

Kesav,

Thank you for editing the formulas, however I tried again and they still returned the wrong answers (just totaling the column and not deducting the amount paid).

As I wasn't able to solve it, I have changed the format of the sheet and will post it as a different question.

Thanks again,


John

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

[JohnFex]

Arlu,

Thanks for your last post, but the problem wasn't actually solved.

As I didn't have any success this way, I redid the sheet and posted an alternative question to try and get round it.

Thanks,


John