February 2, 2013
Hi Every one
i Unable to update the summary details qty in list E column. this qty i need to auto update based on Expo.No & GC.no from summary sheet
Here is some condition :
1. Qty should be update as per Expo.No & GC.no
2. list E-column qty not greater than D-column
3. If F-column balance qty nil, data need to update next line.
December 7, 2016
I see no formula in column E, what have you tried with? Where do you get stuck?
Condition 1 - You can use SUMPRODUCT to sum the matching rows in summary sheet as per the criterias. You do of course need to count for if qty has been set in previous rows (condition 3).
Condition 2 - With an IF you can check if the resulting value from the SUMPRODUCT is greater or less than the qty in column E, if gt then use the value in column D, if lt then use same formula you use when doing the logical test in the IF formula.
Condition 3 - You can use SUMIFS to sum the qty's already given in previous rows for column E, then of course subtract that sum with what you get from the SUMPRODUCT formula (condition 1).
There are many ways to solve things like this, in the attached file I have only provided one way to do it. Hope it works as you want it to.