• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Table Help

A

Anbuselvam K

Member
Hi,

As per attached sheet i got my products stock as on today but i would like to know the stock of product in specific earlier date.

Please guide me to get the product stock on 01-May-2015 in any of the sheet like master data or pivot table.

Thanks in advance.
 

Attachments

Last edited:
I'd suggest adding a helper column near the data. You can hide the column if desired. Then, you can add the helper column as a page field into the PivotTable to get the results you want.
 

Attachments

Hi,

Thanks for your reply....!

I've checked your attachment with new helper column. But i didn't get what i want.

Actually i need a product stock as on 1-May-2015. and the answer is as below.

Answer is.
Product1 120
Product2 30
Product3 30
Product4 60

Logic: Totally we produced 120, 40, 40 and 60Nos. of Product1, 2, 3 and 4. In that we dispatched product2 10nos. on 22-Mar-2015 and Product3 10Nos. on 21-Apr-2015. So as on 1-May-2015 we have a stock as above answer.

If i enter the date 01-May-2015 to any specific cell in that sheet then i should get the stock as above.

I hope you understand my question and please suggest accordingly.
 
Last edited:
You're data does not match what you just said. In Raw Data, if we look at just Product1:
upload_2015-11-10_9-16-22.png
You only have 3 cells, with a sum of 30, that are before May 1. Where are you getting 120??
 
Dear Mr.Luke

Thanks for your continue support.

I've attached same file with clear and easy summary. Please check the same.

Up to 18-May-2015 we produced 200 Numbers of products and there is no sales up to 19-May-2015 (Column N). So stock as on 19-May-2015 should be 200 Numbers. Am i correct...?

In that case with your helper column how do we get that stock..? Please explain to me or change anything to get the same..?

Thanks in advance for your valuable support.
 

Attachments

Hi,

As per attached file and your suggestion i got finished product stock in specific given date. Now i want a stock of RM1, RM2, RM3 and RM4. Master data sheet column H,I,J and K are the consumption and RM Receipt sheet there is a inventory by date wise.

My expected result in Column V, V8:V11 by given date in K1.
 

Attachments

Assuming the dates for consumption are in col A, formula in V8 would be:
=SUMIFS('RM Receipt'!$C$6:$C$34,'RM Receipt'!$B$6:$B$34,U8,'RM Receipt'!$A$6:$A$34,"<"&$K$1)
-SUMIF($A$7:$A$32,"<"&$K$1,INDEX($H$7:$K$32,,MATCH(U8,$H$6:$K$6,0)))
 
You must log in or register to reply here.
Back
Top