I need to replace the data the agt_amt from the Billing worksheet with the value in the Adding of the BalanceSheet.Difference to the Billing.Agt_Amt when the Validated Adj = Y. I have been playing with the Match and if not isblank formula but need some assistance with the proper syntax.
Restating problem I need to check if the value has been validated hence the Validate=Y, Then Adjust the value of the Billing.agt_amt by the value of the qryDifference.Differernce.
Say for Craig W his agent amt 195.75 and he has been validated to need and adjustment in the amount due (-5.50) to him. I need to adjust the agent amount to be 190.25 on the original Billing.
So I thought to create a new Column on the Billing worksheet with the adjusted value and the original value then copy the column over the original prior to finalizing the worksheet.
=MATCH([@agtno],qryDifference[agtno],IF(NOT(ISBLANK(qryDifference[Validate Adjustment])),qryDifference[Difference],0))
Solution found Elsewhere:
=IF([@agtno]=B24,[@[agt_amt]],SUMPRODUCT((Balance!$B$2:$B$7=[@agtno])*(Balance!$F$2:$F$7="Y")*Balance!$E$2:$E$7)+[@[agt_amt]])
