• 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.

An Excel Formula or way to pull the month that a pivot table is filtered by

I'm wondering if there is a way to pull the months that are being filtered in a pivot table. I want to dynamically bring up budget data for the months selected in a pivot table of actual data. In other words, my pivot table is actual expense data, and I would like to pull budget data dynamically for the same date range that is displayed in the pivot table.

Thank you!

Gary
 
If your pivot table has a grand total somewhere, try double-clicking on that total. This should open up a new sheet with just the data that was used to create the pivot table.

Otherwise supply a file and a few more details.
 
Attached is a sample of what I'm trying to do. Any assistance would be greatly appreciated. I have include more detail about the situation in the workbook itself.

Thank you!

Gary
 

Attachments

  • Example for Pulling Filtered Dates from Pivot Table.xlsx
    22.2 KB · Views: 6
By the way, I know about the double click option for bringing up pivot detail, but I don't want the user to have to do that, and then there wouldn't be formulas in place to pull data from that new worksheet.

Thank you though! That is a nice trick.

Gary
 
Terrific! So it appears you have connected the two pivot tables. That is great! How did you do that? Also, regarding the first on, is "SelectedSlicerItems" a User Defined Function, or a standard function in Excel?

Gary
 
Amazing! I have practically lived in Excel for the last 8 years and I still find awesome new tricks every now and then. Thank you very much for your assistance with this challenge. I didn't know you could copy and paste a pivot table to create a link between them. I'm not quite sure why your formula, that looks so simple, works so well. Did the month values need to be row values? Was that part of the issue?

Gary
 
I tried this with my actual pivot table and the pasted one doesn't update when the first/primary one is filtered. Any idea what might be wrong? Unfortunately I can't share this one. Does it matter which part, or all, of the pivot table that you select when copying?

Thank you!

Gary
 
I don't know, I selected the whole pivot table and copy/pasted. However, to ensure connections, right-click the slicer, choose PivotTable connections… and tick the appropriate tick boxes. The pivots have to use the same pivotcache (as far as I know; copy/pasting an entire pivot ensures this).

I'm not quite sure why your formula, that looks so simple, works so well. Did the month values need to be row values? Was that part of the issue?

Which formula?
 
Okay, I think I figured it out- thank you again! Initially I didn't have a slicer for Months, although I did have a Timeline. I removed the Timeline and added a slicer, and then made sure the report connections were set. Now it works. I do have a follow-up question. The got rid of the Month filter and added one that has both the month and the year, i.e. APR 2016, MAR 2016, etc..., all the way back to OCT 2015 at this point. The problem is that the order the months are listed is funky. The months are in alphabetical order, not oldest to newest, or even newest to oldest. I've searched for a fix for this, but haven't been able to find one yet.

Thank you!

Gary
 
In the attachment I have only changed the formula for the MONTH/YEAR column of Table 22. It now has the 1st of the month there as a real date formatted to show only Month Year. I haven't checked if it affects anything else. Over to you.
 

Attachments

  • Detailed Expense Report, Sample for Excel Assistance.xlsx
    106.9 KB · Views: 6
Thank you once again. The only challenge now is that dates are showing in the filter selection with a numeric format, and I'd really prefer the month name followed by the year. Is this possible?

Gary
 
I doubt it, but Ill review tomorrow.
Aren't all the selections being made via the slicer?
 
Thank you! Ideally, yes, they would all be made via the slicer, but as they say, it's hard to teach old dogs... If there isn't a way to change the data format for the drop down list I'll get used to it.

Have a great day!

Gary
 
In the file I attached last time, right-click somewhere in the pivot table on the Pivot Table sheet and click Refresh.
Right click on the cell C8 (not its dropdown) and choose Field Settings…, at the bottom of the resulting dialogue box you should see a Number Format button, click it and use a custom format of mmm yy . If a Number Format button doesn't show, come back.
 
You are a genius!! Thank you so much! I was looking for that little number format option, but couldn't find it anywhere.

Have a great evening!

Gary
 
Back
Top