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 1-Jan a 12 4-Mar b 13 4-May c 14 7-Sep d 15 8-Mar e 16 9-Aug f 17 12-Dec 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 > 1-Jan a 12 > 4-Mar b 13 > 4-May c 14 > 7-Sep d 15 > 8-Mar e 16 > 9-Aug f 17 > 12-Dec 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]  |