LinkBack URL
About LinkBacks
Hi,
Have attached the sheet with the payment Dedution Requirement.Pls have a look and help me out.Let me know for the questions if any.
Thanks
Hi,
Have attached the sheet with the payment Dedution Requirement.Pls have a look and help me out.Let me know for the questions if any.
Thanks
Last edited by laxmanann; 09-18-2014 at 08:40 AM.
Can anyone please have a look and share your valuavble logic here.
Thanks,
Dear masters,
Please help me on the above thread..Looking for your help ..
Thanks
I can get you partially through this with formulas.
On Sheet1, in D2 copied down
=IF(COUNTIFS($C$2:$C$15,"<0",$B$2:$B$15,B2)<1,0,MIN(0,MAX(SUMIFS($C$2:$C$15,$C$2:$C$15,"<0",$B$2:$B$15,B2)-SUMIF($B$1:$B1,B2,$D$1:$D1),-C2)))
Then you'll need a macro (or manually perform)
Convert the formulas in column D to constants (Copy>Paste Special >Values)
Delete rows that have negative numbers in Col C
Insert a similar formula in Col F
=IF(COUNTIFS($E$2:$E$15,"<0",$B$2:$B$15,B2)<1,0,MIN(0,MAX(SUMIFS($E$2:$E$15,$E$2:$E$15,"<0",$B$2:$B$15,B2)-SUMIF($B$1:$B1,B2,$F$1:$F1),-E2)))
See attachment
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Thanks Guru ChemistB.Pls find the attachement for the sheet1 and pls help me to complete this activity.
Thanks ,
laxman
Can you upload the example with the columns filled in manually as you would expect your final sheet to look like? Maybe color those cells so we know those are cells where you'd expect automation.
Dear ChemistB,
Same Concept of the previous one.There we have worked on single sheet..Here i need to make adjustments with the 2 sheets like Sheet1,Sheet2.
Here, we have 2 sheets and need to adjust sheet1 ACp pay by sheet 2 ACp pay.
For 1st Rd Adj we can use the formula as we did already.No changes needed.
1.Take the amount against the ACP name in sheet2(subtoal of ACPname-amount if can) and adjust the amount in sheet1 in " 2nd Rd adj column"
2.If column B(ACP name) in sheet2 is not availabe in sheet1,mark comment as "NA"
3.If Acp pay mentioned in sheet2 is fully adjusted with sheet1 fine.. if not try to adjust the amount partially and mark comment as "partially adjusted"
(Eg. in sheet2 subtotal of ACp pay is 10 for India but in sheet1 the total india's acp amount is 7,then adjust 7 in sheet1(2nd rd adj column) and mark the comment in sheet2 as ''PA'' against the item
Thanks..
Here is a formula for column D. I am not sure if you want to separate by "Account" (which is what this formula does) or by "Acp Name". Again, this is about as far as I can take you. Not even sure what you would do with Column F since all the negatives from sheet 2 have been taken care of in col D.
I asked you to upload an example with what you expected in each cell and you did not do that so I do not have any way of knowing how close or far off I am.
I think (maybe) I understand what you want. Please try the following code. You gave no direction that I saw regarding what to do with the 2nd Adj columns, so I didn't do anything with that, or if the final 1st Adj still comes out negative. You will need to format your column D as Number to make the results look consistent with the others.
Please Login or Register to view this content.
Acts 4:12
Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks