help vlookup

[takagi]

in 'update' sheet, F column, the current value of quantity should be added with

value in 'IN' sheet, D column.


in 'IN' sheet if i choose an item from dropbox (IN!c6) and put quantity of 10 (IN!d6),

then it should be added with the current value in 'UPDATE'sheet (UPDATE!F7)for the same named item.


i tried this formula (this is nonsense formula come to my head) but fail in 'update!f7, sheet


=f7+(vlookup,purchase,2,false)

http://www.excelforum.com/attachment.php?attachmentid=223647&d=1364267941

 

[NARAYANK991]

Hi ,


I doubt that what you want can be done with only formulae , since each transaction needs to be treated separately and preserved.


If on any particular date , the stock-in-hand is 10 for a particular item , if a purchase is made and a quantity of 5 is entered in the IN tab , the stock-in-hand should change to 15 as on that date. When a quantity is issued , then the stock should reduce by the issued quantity.


Using the dates of purchase and issue on an ongoing basis is going to make the formulae quite complicated , since all purchases ( of different items ) will be on the same tab. The same is the case with issues , since issues of different items will be on the same tab.


VBA would make the whole task quite easy , since to get the up-to-date status , you can run a macro which does all the processing of purchases and issues , and presents a consolidated status.


Narayan