GETPIVOTDATA to create Purchase in specified month

Dear All,

I have a pivottable about Purchasing Data, contains fields such as Supplier
Name, PO #, PO Date, Unit Price, Qty and Total Price.

I would like to create a report/list, when a user click (using Combo Box to
certain Month), so it will create a report Purchasing done in that specified
month.

The question is how to create this report using GETPIVOTDATA based on that
month with summarized by Supplier name.

Please advice.

Thank you.

Andri

Hi Andri

I don't think you necessarily need to use the Getpivotdata option to achieve
your requirement.
In your Pivot Table, group PO Date by Month.
Then drag the PO Date field to the Page area of the PT report.
Selecting any given month from the dropdown list of months created will show
just the data by Supplier for that month.

--
Regards
Roger Govier
"Andri" <Andri@discussions.microsoft.com> wrote in message
news:ECE945E6-9C17-4D95-92AE-9DBEE1CC28E1@microsoft.com...
> Dear All,
>
> I have a pivottable about Purchasing Data, contains fields such as
> Supplier
> Name, PO #, PO Date, Unit Price, Qty and Total Price.
>
> I would like to create a report/list, when a user click (using Combo Box
> to
> certain Month), so it will create a report Purchasing done in that
> specified
> month.
>
> The question is how to create this report using GETPIVOTDATA based on that
> month with summarized by Supplier name.
>
> Please advice.
>
> Thank you.
>
> Andri

Dear Roger,
Thank you for your advice. But i would like to send the Purchase Report in
Certain Format.
Where i would like to hide the sheet of the pivot table in PROTECTED SHEET
is on, while my boss just click the combo box to select certain month based
on the format i have prepared.

So i have prepared the format of the report, if the boss wish to know by
yearly, so all data will be created for the whole year.
If he would like to know on monthly basis, he just click the month. and the
report will be updated accordingly

For him, adjusting the Data field looks like complicated.

This report just will give a lot of information when the user click the
combo box,
such as how many PO issued, back order, supplier deal for certain month,
Lines Items that purchased.

Thank you.

"Roger Govier" wrote:

> Hi Andri
>
> I don't think you necessarily need to use the Getpivotdata option to achieve
> your requirement.
> In your Pivot Table, group PO Date by Month.
> Then drag the PO Date field to the Page area of the PT report.
> Selecting any given month from the dropdown list of months created will show
> just the data by Supplier for that month.
>
> --
> Regards
> Roger Govier
> "Andri" <Andri@discussions.microsoft.com> wrote in message
> news:ECE945E6-9C17-4D95-92AE-9DBEE1CC28E1@microsoft.com...
> > Dear All,
> >
> > I have a pivottable about Purchasing Data, contains fields such as
> > Supplier
> > Name, PO #, PO Date, Unit Price, Qty and Total Price.
> >
> > I would like to create a report/list, when a user click (using Combo Box
> > to
> > certain Month), so it will create a report Purchasing done in that
> > specified
> > month.
> >
> > The question is how to create this report using GETPIVOTDATA based on that
> > month with summarized by Supplier name.
> >
> > Please advice.
> >
> > Thank you.
> >
> > Andri
>
>
>

Your boss won't have to adjust the Date field -- all he has to do is
select a month from a dropdown list in the Page area.

You set up the grouped Date, as Roger explained, then the field won't
have to be adjusted again.

Andri wrote:
> Dear Roger,
> Thank you for your advice. But i would like to send the Purchase Report in
> Certain Format.
> Where i would like to hide the sheet of the pivot table in PROTECTED SHEET
> is on, while my boss just click the combo box to select certain month based
> on the format i have prepared.
>
> So i have prepared the format of the report, if the boss wish to know by
> yearly, so all data will be created for the whole year.
> If he would like to know on monthly basis, he just click the month. and the
> report will be updated accordingly
>
> For him, adjusting the Data field looks like complicated.
>
> This report just will give a lot of information when the user click the
> combo box,
> such as how many PO issued, back order, supplier deal for certain month,
> Lines Items that purchased.
>
> Thank you.
>
> "Roger Govier" wrote:
>
>
>>Hi Andri
>>
>>I don't think you necessarily need to use the Getpivotdata option to achieve
>>your requirement.
>>In your Pivot Table, group PO Date by Month.
>>Then drag the PO Date field to the Page area of the PT report.
>>Selecting any given month from the dropdown list of months created will show
>>just the data by Supplier for that month.
>>
>>--
>>Regards
>>Roger Govier
>>"Andri" <Andri@discussions.microsoft.com> wrote in message
>>news:ECE945E6-9C17-4D95-92AE-9DBEE1CC28E1@microsoft.com...
>>
>>>Dear All,
>>>
>>>I have a pivottable about Purchasing Data, contains fields such as
>>>Supplier
>>>Name, PO #, PO Date, Unit Price, Qty and Total Price.
>>>
>>>I would like to create a report/list, when a user click (using Combo Box
>>>to
>>>certain Month), so it will create a report Purchasing done in that
>>>specified
>>>month.
>>>
>>>The question is how to create this report using GETPIVOTDATA based on that
>>>month with summarized by Supplier name.
>>>
>>>Please advice.
>>>
>>>Thank you.
>>>
>>>Andri
>>
>>
>>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Dear Debra/Roger,

Here the situation:
I have a Sheet "Report" with this information.

Row 20: Total Purchase in Year 2,864,441 (Use GETPIVOTDATA Function)
Row 21: Total Supplier List 52
Row 23: Total PO Issued 157
Row 24: Total Part Number Issued 805

So when the boss, click the year 2003 (With Using Combo Box).
That four fields will automatically changes.
Even he click Monthly purchase instead of Yearly purchase, the four fields
will updated automatically.

For example, when he click monthly purchase in September 2003, Total
Supplier list will be update (e.g) 10 vendors (Related to Row 21).

I have problem how to display the details of that fields.
Let we say for ROW 26 - XXX, i want to display those 10vendors AUTOMATICALLY
in Sheet "REPORT" instead of Sheet "PIVOTTABLE"

I wish my boss not click on the PIVOTABLE Sheet, but i would like him just
to click the COMBO BOX I have provided.

Thank you for your help.

Andri




"Debra Dalgleish" wrote:

> Your boss won't have to adjust the Date field -- all he has to do is
> select a month from a dropdown list in the Page area.
>
> You set up the grouped Date, as Roger explained, then the field won't
> have to be adjusted again.
>
> Andri wrote:
> > Dear Roger,
> > Thank you for your advice. But i would like to send the Purchase Report in
> > Certain Format.
> > Where i would like to hide the sheet of the pivot table in PROTECTED SHEET
> > is on, while my boss just click the combo box to select certain month based
> > on the format i have prepared.
> >
> > So i have prepared the format of the report, if the boss wish to know by
> > yearly, so all data will be created for the whole year.
> > If he would like to know on monthly basis, he just click the month. and the
> > report will be updated accordingly
> >
> > For him, adjusting the Data field looks like complicated.
> >
> > This report just will give a lot of information when the user click the
> > combo box,
> > such as how many PO issued, back order, supplier deal for certain month,
> > Lines Items that purchased.
> >
> > Thank you.
> >
> > "Roger Govier" wrote:
> >
> >
> >>Hi Andri
> >>
> >>I don't think you necessarily need to use the Getpivotdata option to achieve
> >>your requirement.
> >>In your Pivot Table, group PO Date by Month.
> >>Then drag the PO Date field to the Page area of the PT report.
> >>Selecting any given month from the dropdown list of months created will show
> >>just the data by Supplier for that month.
> >>
> >>--
> >>Regards
> >>Roger Govier
> >>"Andri" <Andri@discussions.microsoft.com> wrote in message
> >>news:ECE945E6-9C17-4D95-92AE-9DBEE1CC28E1@microsoft.com...
> >>
> >>>Dear All,
> >>>
> >>>I have a pivottable about Purchasing Data, contains fields such as
> >>>Supplier
> >>>Name, PO #, PO Date, Unit Price, Qty and Total Price.
> >>>
> >>>I would like to create a report/list, when a user click (using Combo Box
> >>>to
> >>>certain Month), so it will create a report Purchasing done in that
> >>>specified
> >>>month.
> >>>
> >>>The question is how to create this report using GETPIVOTDATA based on that
> >>>month with summarized by Supplier name.
> >>>
> >>>Please advice.
> >>>
> >>>Thank you.
> >>>
> >>>Andri
> >>
> >>
> >>
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

To list the vendors, you'd need programming, to filter for vendors
active in a selected month/year, and add formulas to calculate the
amount per vendor.

You could record a macro as you perform the steps manually, then modify
the recorded code where required.

Andri wrote:
> Dear Debra/Roger,
>
> Here the situation:
> I have a Sheet "Report" with this information.
>
> Row 20: Total Purchase in Year 2,864,441 (Use GETPIVOTDATA Function)
> Row 21: Total Supplier List 52
> Row 23: Total PO Issued 157
> Row 24: Total Part Number Issued 805
>
> So when the boss, click the year 2003 (With Using Combo Box).
> That four fields will automatically changes.
> Even he click Monthly purchase instead of Yearly purchase, the four fields
> will updated automatically.
>
> For example, when he click monthly purchase in September 2003, Total
> Supplier list will be update (e.g) 10 vendors (Related to Row 21).
>
> I have problem how to display the details of that fields.
> Let we say for ROW 26 - XXX, i want to display those 10vendors AUTOMATICALLY
> in Sheet "REPORT" instead of Sheet "PIVOTTABLE"
>
> I wish my boss not click on the PIVOTABLE Sheet, but i would like him just
> to click the COMBO BOX I have provided.
>
> Thank you for your help.
>
> Andri
>
>
>
>
> "Debra Dalgleish" wrote:
>
>
>>Your boss won't have to adjust the Date field -- all he has to do is
>>select a month from a dropdown list in the Page area.
>>
>>You set up the grouped Date, as Roger explained, then the field won't
>>have to be adjusted again.
>>
>>Andri wrote:
>>
>>>Dear Roger,
>>>Thank you for your advice. But i would like to send the Purchase Report in
>>>Certain Format.
>>>Where i would like to hide the sheet of the pivot table in PROTECTED SHEET
>>>is on, while my boss just click the combo box to select certain month based
>>>on the format i have prepared.
>>>
>>>So i have prepared the format of the report, if the boss wish to know by
>>>yearly, so all data will be created for the whole year.
>>>If he would like to know on monthly basis, he just click the month. and the
>>>report will be updated accordingly
>>>
>>>For him, adjusting the Data field looks like complicated.
>>>
>>>This report just will give a lot of information when the user click the
>>>combo box,
>>>such as how many PO issued, back order, supplier deal for certain month,
>>>Lines Items that purchased.
>>>
>>>Thank you.
>>>
>>>"Roger Govier" wrote:
>>>
>>>
>>>
>>>>Hi Andri
>>>>
>>>>I don't think you necessarily need to use the Getpivotdata option to achieve
>>>>your requirement.
>>>>In your Pivot Table, group PO Date by Month.
>>>>Then drag the PO Date field to the Page area of the PT report.
>>>>Selecting any given month from the dropdown list of months created will show
>>>>just the data by Supplier for that month.
>>>>
>>>>--
>>>>Regards
>>>>Roger Govier
>>>>"Andri" <Andri@discussions.microsoft.com> wrote in message
>>>>news:ECE945E6-9C17-4D95-92AE-9DBEE1CC28E1@microsoft.com...
>>>>
>>>>
>>>>>Dear All,
>>>>>
>>>>>I have a pivottable about Purchasing Data, contains fields such as
>>>>>Supplier
>>>>>Name, PO #, PO Date, Unit Price, Qty and Total Price.
>>>>>
>>>>>I would like to create a report/list, when a user click (using Combo Box
>>>>>to
>>>>>certain Month), so it will create a report Purchasing done in that
>>>>>specified
>>>>>month.
>>>>>
>>>>>The question is how to create this report using GETPIVOTDATA based on that
>>>>>month with summarized by Supplier name.
>>>>>
>>>>>Please advice.
>>>>>
>>>>>Thank you.
>>>>>
>>>>>Andri
>>>>
>>>>
>>>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html