Help with lookup

[rosey]

Hi All,

I need some assistance with vlookup.

I have setup a stock database for work. We currently do transactions on one sheet with quantity. I then bring the quantity number over to another sheet matching the part number on one sheet with the other. From there, I will subtract the current quantity with the new quantity I just put in to give my new level of stock.

I currently use this formula:
=IF(ISNA(VLOOKUP(A4,Dispatched!$D$4:$E$20000,2,0)),0,VLOOKUP(A4,Dispatched!$D$4:$E$20000,2,0))

The issue is that I can only do this once and it will be needed multiple times.
For example, if on sheet B I have two entries of the same part number on different rows with different quanities, I need this number added together and then brough ovwer to sheet A so I can calculate current levels.

I hope this makes sence, any ideas?

[rosey]

Another thought I had was to add the different quantities based on the part number on sheet B and then bring it over to sheet A.

example of sheet B:


8602820-001 3
3601122-101 12
8609999-001 1
8602820-001 1
7800107-101 1
8602820-001 1 (this will now equal 5, bring this over to sheet A)

Is there anyway I could do this?

[Portuga]

Maybe a sumproduct formula will do the trick:

To sum:
=SUMPRODUCT((Dispatched!$D$4:$D$5000=A4)*Dispatched!$E$4:$E$5000)

To count:
=SUMPRODUCT((Dispatched!$D$4:$D$5000=A4)*1)

[Trebor76]

Hi rosey,

Though I'm not an expert with them, I'd say this scenario lends itself towards the use of a Pivot Table.

That said, you'll probably get by simply by using a SUMIF formula like this:

=SUMIF(Dispatched!D4:E20000,A4,Dispatched!E4:E20000)

HTH

Robert

[rosey]

Thanks guys,

I will test it tomorrow

[rosey]

Thanks Robert, worked great!