Pivot Table for Stock Control - how do I summarise In's and Out's?

Pivot Table for Stock Control - how do I...

Didn't find your answer?

Hi
I've used pivot table in summarizing the stocks-in and stock-out value for each item. At the end of month, I would like to see the accumulated value/Qty of each items, however, when I drop the month field to page field area, it only shows each month's amount when I select month, not the accumulated amount from start of record till the date I select.

If I select "all", it shows all the accumulated amounts till the last date of the source record, so there's no way I can see for example Jan 1st to May 31st or Jan 1st to April 30.

Any guidance in solving this limit is highly appreciated. Thanks in advance for any suggestion.
Ray Yaukip

Replies (5)

Please login or register to join the discussion.

avatar
By dclark
12th Aug 2005 15:35

Move system
Ray

Forgive me for not understanding the challenge here, but why are you not using a stock control system to do all this for you ?

If you are using excel to give you these types of answers then you must have a fair amount of stock movements. If you are using any computerised system to record these movements (receipts, despatches, orders, etc) and it doesn't do this for you then I'd move system to one that does. This is very basic stock movement reporting and any decent system should do this without question. If you are not using any system to record these movements, then I'd get one, becuase to get all this stuff into excel must take ages.

The general challenge with going to excel or access is you are now entering the realms of 'programming' and understanding the errors and choices that come with building software (even if it is using tools in a programme like excel). My opinion is that this should already have been done by your software choice.

I know this is probably an un-heplful rant, but I'd get a system that doess this type of reporting for you and stop building external applications that need constant work

Kind Regards

Daniel Clark
Ryba Macaulay Ltd
[email protected]

Thanks (0)
avatar
By David Carter
25th Jul 2005 10:41

of course pivot tables can do it!
Ray, you don't seem to appreciate that with pivot tables an experienced practitioner can do ANYTHING.

Your problem doesn't seem too difficult and I thought my solution would solve it - maybe I've misunderstood the problem. Any chance of sending me your data and I can work out the answer?

Otherwise (horror of horrors) we'll have to admit that you can only do it with Access.
David Carter [IT consultant editor, [email protected]]

Thanks (0)
avatar
By Richard Willis
14th Jul 2005 13:12

What's the source?
Hi Ray

It's a bit difficult without knowing what the source of the pivot data consists of. Is it entered elsewhere in the Workbook or is it external data?

Certainly if the latter, if the data consists of individual records with dates attached, then if you open the Query you should be able to add a date criteria and adjust it to suit current requirements. If you re-do the pivot, you can select the date field and add the range at source.

There is a facility to add 'Parameters' (I think it's called), which opens a popup to select a range, but with my accounting system these are not allowed and I've never bothered to investigate otherwise. Have a look in the help.

Good luck

Thanks (0)
avatar
By David Carter
14th Jul 2005 17:05

Move MONTH into the Row area
As you say, filtering on Page fields is very primitive. You can only show 1 month or all months. [Microsoft need to upgrade the Page fields filter and offer a "Custom" option as in Autofilter.]

However, I think you can get what you want if you move Month out of the Page area and into the Row area.

Move Month into the Row area, and then click onto the down arrow.

All the months now have ticks against them. Untick the ones you wish to exclude and the accumulated total will be recalculated.

You can then move Month back into the Page area.

[This is for Excel 2003 and Excel 2000. With Excel 97 you use the Hide command]

Thanks (0)
avatar
By neileg
14th Jul 2005 09:08

Not aware
I'm not aware of any way you can achieve this without manipulating the base data which defeats the point of a pivot table.

It is very simple to do this in Access, however.

Thanks (0)