Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Need to Identify Pending qty against Open po qty

0

Hi ,

Hope some can help me

I have set of PO qty which is raised daily, and for the same I will receiving qty partially against each PO's

The challenge I'm facing is to identify the remaining PO qty of a particular PO, ( As the Materials are received partially sometimes receipt will done once in 3 months.

Note : Receiving will not in sequence as there will be multiple materials received against multiple PO's

Sample calculation PO PO qty PO Receiving qty Receiving date Pending PO qty 12345 100000 12345 20000 18-07-2016 80000 56789 50000 12345 10000 20-07-2016 70000     56789 20000 20-07-2016 30000     12345 50000 20-07-2016 Here we want a formula which auotmatically calculate the remaining qty of PO# 12345, when ever this is entry against the PO

excel
MS Office Version: Excel 2103
posted by: manuprasad29 on Wed Jul 20, 2016 at 5:04 am
Answer
Discuss

Answers

1

Try this in cell 'Receiving Qty'!I2.

=SUMIF('PO details'!$B:$B,'Receving qty'!$B2,'PO details'!$E:$E)-SUMIF($B:$B,$B2,$E:$E)

posted by: MAQ (rep: 15) on Wed Jul 20, 2016 at 3:22 pm
Discuss

Discussion

Hi,
Thanks for your anser it's working.

But some times on the same PO we will be having multiple Items and multiple qty, using this formula it's calculating on PO itself, please help on this

I have udpated a new file as well.
manuprasad29 Jul 22, '16 at 3:58 am
OK, this should get you a PO / Item specific qty.

=SUMIFS('PO details'!$F:$F,'PO details'!$C:$C,'Receving qty'!$B2,'PO details'!$E:$E,'Receving qty'!$D2)-SUMIFS($E:$E,$B:$B,$B2,$D:$D,$D2)
MAQ (rep: 15) Jul 22, '16 at 8:51 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login