Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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" wrote in message ... 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 |
#4
|
|||
|
|||
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" wrote in message ... 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 |
#5
|
|||
|
|||
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" wrote in message ... 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 |
#6
|
|||
|
|||
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Month Year Date Format | Excel Worksheet Functions | |||
Accounting Month vs. Calendar Month | Excel Worksheet Functions | |||
Generating a blank worksheet at the start of the month | New Users to Excel | |||
I need to create pivot table for the month but data in days | Excel Discussion (Misc queries) | |||
create a date from year, day and month | Excel Worksheet Functions |