Prev: Sorting a protected set of cells
Next: Please help me to solve the below equation in excel
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]

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