Excel VBA

snhandle
snhandle used Ask the Experts™
on
I would like to explain my problem with example, I have the total amount available 500 for Member ID 122
first row the cost is 400 so it the money available is more than the cost so the cost amount should go to Dept pay
and nothing should go to Mem payment. Then we have 100 dollars left and the cost of Item 2 is 80 so 20 dollars should be payid
by dept and the rest of 60 dollars should go to member payment and since for the third item there is no amount left so the whole 50 dollars should be paid by member. How I can automate this? Is there any VBA?
It should be done by each Member ID. For each member the total amount available is different.
Sample-distribution-amount.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
Your description doesn't quite make sense.

ID 122:
Starting balance 500
Item 1 400 paid by Dept so leaves 100
Item 2 80, why is this not coming from the balance of 100 and is being split across member and dept? If paid by dept would leave a balance of 20
Item 3 50, use balance of 20 and the member pays the remaining 30
Finance Analyst
Commented:
Assuming the distribution should be as I have pointed out above, ie use available amount until spent and then distribute into member payment, see attached using SUMIF function.
Sample-distribution-amount.xlsx

Author

Commented:
big help!! thanks
Rob HensonFinance Analyst

Commented:
No worries, glad to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial