Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Page Field
I am trying to select multiple items from the drop down list in the page
field of my Pivot Table. Instead of just the "show all" option - I'd like to be able to display the checkboxes for selecting individual items in the drop down list. The page field represents "Months" in the report and the file will be sent to our customers - as a tool to help them track their sales. If possible I'd like to avoid them having to go into the field settings and hide individual months so that they can view a specific time period. I've tried rearranging it, but it looks very sloppy any other way. Any suggestions? Thanks- Nate R. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Page Field
You may be able to create a macro that creates your pivot table. Then in the
code, you could set a variable for the month or an input box to have the month entered. Something like this: Dim wb as workbook Dim curMnth as integer 'For setting the date, you can either create a msgbox to have the user enter the date, or you can set the month in code. (This should only be used if you are always going to run the report for the prior month.) curMnth = inputbox("What month are the reports being run for?", msglbl, vbokonly) -- OR -- curMnth = Format(Date,"MM")-1 This will return the value for the previous month With wb.PivotTables("PivotTable1").PivotFields("Date") .Orientation = xlRowField .Position = 1 .value = curMnth End With "Nate" wrote: I am trying to select multiple items from the drop down list in the page field of my Pivot Table. Instead of just the "show all" option - I'd like to be able to display the checkboxes for selecting individual items in the drop down list. The page field represents "Months" in the report and the file will be sent to our customers - as a tool to help them track their sales. If possible I'd like to avoid them having to go into the field settings and hide individual months so that they can view a specific time period. I've tried rearranging it, but it looks very sloppy any other way. Any suggestions? Thanks- Nate R. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Page Field
Hi Nate
In XL2007 there is the option to select Multiple items for a page field. For earlier versions, Drag the Months from Page field to Row field Click the dropdown on Months, deselect All and make your Months selections Drag the field back to Page, and it will Show (Multiple Items), and clicking the dropdown only the Months you selected will be visible -- Regards Roger Govier "Nate" wrote in message ... I am trying to select multiple items from the drop down list in the page field of my Pivot Table. Instead of just the "show all" option - I'd like to be able to display the checkboxes for selecting individual items in the drop down list. The page field represents "Months" in the report and the file will be sent to our customers - as a tool to help them track their sales. If possible I'd like to avoid them having to go into the field settings and hide individual months so that they can view a specific time period. I've tried rearranging it, but it looks very sloppy any other way. Any suggestions? Thanks- Nate R. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Page Field
Thanks for the suggestion. Sounds like it should work. I'll run it by my
boss to see if it's something we can use. Have a great Holiday! "Roger Converse" wrote: You may be able to create a macro that creates your pivot table. Then in the code, you could set a variable for the month or an input box to have the month entered. Something like this: Dim wb as workbook Dim curMnth as integer 'For setting the date, you can either create a msgbox to have the user enter the date, or you can set the month in code. (This should only be used if you are always going to run the report for the prior month.) curMnth = inputbox("What month are the reports being run for?", msglbl, vbokonly) -- OR -- curMnth = Format(Date,"MM")-1 This will return the value for the previous month With wb.PivotTables("PivotTable1").PivotFields("Date") .Orientation = xlRowField .Position = 1 .value = curMnth End With "Nate" wrote: I am trying to select multiple items from the drop down list in the page field of my Pivot Table. Instead of just the "show all" option - I'd like to be able to display the checkboxes for selecting individual items in the drop down list. The page field represents "Months" in the report and the file will be sent to our customers - as a tool to help them track their sales. If possible I'd like to avoid them having to go into the field settings and hide individual months so that they can view a specific time period. I've tried rearranging it, but it looks very sloppy any other way. Any suggestions? Thanks- Nate R. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Page Field
Good to know that it won't be an issue in XL2007, and you're suggestion
sounds like it is the easiest way to accomplish what we want for the time being. I appreciate the input. Have a great Holiday! Nate "Roger Govier" wrote: Hi Nate In XL2007 there is the option to select Multiple items for a page field. For earlier versions, Drag the Months from Page field to Row field Click the dropdown on Months, deselect All and make your Months selections Drag the field back to Page, and it will Show (Multiple Items), and clicking the dropdown only the Months you selected will be visible -- Regards Roger Govier "Nate" wrote in message ... I am trying to select multiple items from the drop down list in the page field of my Pivot Table. Instead of just the "show all" option - I'd like to be able to display the checkboxes for selecting individual items in the drop down list. The page field represents "Months" in the report and the file will be sent to our customers - as a tool to help them track their sales. If possible I'd like to avoid them having to go into the field settings and hide individual months so that they can view a specific time period. I've tried rearranging it, but it looks very sloppy any other way. Any suggestions? Thanks- Nate R. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Page field - pivot table | Excel Discussion (Misc queries) | |||
Controlling Pivot Table Page-Field | Excel Discussion (Misc queries) | |||
Pivot Table Page Field | Excel Discussion (Misc queries) | |||
Pivot Table Page Field | Excel Discussion (Misc queries) | |||
Pivot Table - Filtering Page Field | Excel Discussion (Misc queries) |