From: vas on 24 Dec 2009 05:00 Hi when i enter an item the stock should be reduced by the quantity and whn i enter the same item quantity again then stock should be reduced from the remaining quantity. how will i do that...eg item a is entered twice 1st 12 quantity, so the stock should be 2 and i entered a again in the last 1quantity. so the remaining stock should be 1 Date Item Qty 1Jan a 12 4Mar b 13 4May c 14 7Sep d 15 8Mar e 16 9Aug f 17 12Dec g 17 Item Stock a 14 b 15 c 16 d 17 e 18 f 19 g 19 Waiting for the suggestions. we cannot use the more than 7 nested loops. So how will we solve this.
From: teylyn on 26 Dec 2009 03:52 Hi, you could combine the starting balance of Item Stock with a SUMIF() formula. For example, if Item a has a starting stock of 14, put this formula in the Stock sheet for Item a: =14(SUMIF(Data!$B$2:$B$100,"a",Data!$C$2:$C$100) where column B is the Item and column C is the turnover on the Data sheet. hth teylyn vas;595320 Wrote: > Hi > when i enter an item the stock should be reduced by the quantity and > whn i > enter the same item quantity again then stock should be reduced from > the > remaining quantity. how will i do that...eg item a is entered twice 1st > 12 > quantity, so the stock should be 2 and i entered a again in the last > 1quantity. so the remaining stock should be 1 > > Date Item Qty > 1Jan a 12 > 4Mar b 13 > 4May c 14 > 7Sep d 15 > 8Mar e 16 > 9Aug f 17 > 12Dec g 17 > > > Item Stock > a 14 > b 15 > c 16 > d 17 > e 18 > f 19 > g 19 > > > Waiting for the suggestions. we cannot use the more than 7 nested > loops. So > how will we solve this.  teylyn Teylyn  'teylyn.posterous.com' (http://teylyn.posterous.com)  teylyn's Profile: 983 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=164960 [url="http://www.thecodecage.com"]Microsoft Office Help[/url]

Pages: 1 Prev: Sorting a protected set of cells Next: Please help me to solve the below equation in excel 