Jyotirmaya
Board Regular
- Joined
- Dec 2, 2015
- Messages
- 204
- Office Version
- 2019
- Platform
- Windows
I have 3 sheets
Sheet1 name - Demand
Sheet2 name - Collection
Sheet3 Name - Balance
Demand means A user is having some Due for a particular product
Collection means the amount collected from that person for that product
Balance means the person is due the rest amount i.e Balance = Demand - Collection
There are some persons also there who paid the amount more than due and those amount were kept reserved in the Column Excess Unadjusted amount in the Balance sheet.
There are persons who paid less than the demand amount are stored in the Balance sheet after deducting the Demand from that person- collection from that person.
There are some rules to be followed while getting the Balance amount results are
1.If there is a demand in User ID and collection is done same as per the demand amount and unadjusted amount is also there, then no changes in unadjusted amount, balance will be zero.
Example If there is a demand in Sheet in A1 is 5 collection is 5 inA1 and in balance sheet D2 Unadjusted amount is 10 then no changes in the unadjusted amount balance will be zero. i.e in Balance sheet B2-B3 = 0
2.If there is a demand in User ID and collection is done more than demand then that, then the excess amount will be added to excess unadjusted column.
Example If there is a demand in Sheet in A1 is 5 and collection is Rs 10 in A1 then Rs 5 will be shown as collection B3 in Balance sheet and Extra Rs 5 collected will be shown as Excess unadjusted column in D2 i.e in Balance sheet B2-B3=0
3. If there is a demand in current year of a User and collection is not there for that current year and if Excess unadjusted amount is available then that amount will be adjusted according to the demand amount, the adjusted amount should show in a new column and the rest unadjusted amount will be keep reserved after deduction of demand amount.
Example If Demand Sheet in A1 is 5 collection sheet is 0 of A1 and in balance sheet D2 unadjusted amount available 10 then Rs5 will be Adjusted and Rs 5 will be shown on C2 of Balance sheet as Adjusted amount i.e in Balance sheet B2-C2 = 0 & D2 will be 5
I want a macro to fulfill all such conditions.
Sheet1 name - Demand
Sheet2 name - Collection
Sheet3 Name - Balance
Demand means A user is having some Due for a particular product
Collection means the amount collected from that person for that product
Balance means the person is due the rest amount i.e Balance = Demand - Collection
There are some persons also there who paid the amount more than due and those amount were kept reserved in the Column Excess Unadjusted amount in the Balance sheet.
There are persons who paid less than the demand amount are stored in the Balance sheet after deducting the Demand from that person- collection from that person.
There are some rules to be followed while getting the Balance amount results are
1.If there is a demand in User ID and collection is done same as per the demand amount and unadjusted amount is also there, then no changes in unadjusted amount, balance will be zero.
Example If there is a demand in Sheet in A1 is 5 collection is 5 inA1 and in balance sheet D2 Unadjusted amount is 10 then no changes in the unadjusted amount balance will be zero. i.e in Balance sheet B2-B3 = 0
2.If there is a demand in User ID and collection is done more than demand then that, then the excess amount will be added to excess unadjusted column.
Example If there is a demand in Sheet in A1 is 5 and collection is Rs 10 in A1 then Rs 5 will be shown as collection B3 in Balance sheet and Extra Rs 5 collected will be shown as Excess unadjusted column in D2 i.e in Balance sheet B2-B3=0
3. If there is a demand in current year of a User and collection is not there for that current year and if Excess unadjusted amount is available then that amount will be adjusted according to the demand amount, the adjusted amount should show in a new column and the rest unadjusted amount will be keep reserved after deduction of demand amount.
Example If Demand Sheet in A1 is 5 collection sheet is 0 of A1 and in balance sheet D2 unadjusted amount available 10 then Rs5 will be Adjusted and Rs 5 will be shown on C2 of Balance sheet as Adjusted amount i.e in Balance sheet B2-C2 = 0 & D2 will be 5
I want a macro to fulfill all such conditions.
