Pivot Table to filter based on cell value on different worksheet

**kingofcamden** · 09-25-2018, 01:15 PM

Hi all,

I am working on a report and am trying to get a pivot table (on a hidden spreadsheet) to filter based on a value that I select from a dropdown on a different worksheet (so basically the content of a cell on a different worksheet). I found a few VBA scripts to do this, however, none of them worked. One example:

Please Login or Register  to view this content.

(found here: http://dedicatedexcel.com/how-to-con...alue-with-vba/)

The cell that it should look out for is on sheet "LC_Dashboard" cell "O44". The pivot table to control is on a worksheet called "Pivot_C_WoW" and the value that it should filter on is called "Discount". Manually filtering the pivot based on discount works (just added the discount field to the Filters in the pivot and then selected the correct discount), however, when I use this script (changed the values to match mine, of course) and change the discount value on the LC_Dashboard it just doesn't update...

Not sure what I am doing wrong. Any help would be appreciated

Thanks!

**CK76** · 09-25-2018, 01:49 PM

How to interact with Pivot via code, will depend on your pivot table type.

If using Excel Table based Pivot (traditional), then above code should work. Assuming filter is not based on dates, if dates based, you should use CStr() or CDate() to convert (which depends on set up).

If using OLAP based (data model as data source for pivot), then code will significantly change from sample.

I'd recommend you upload sample workbook, which mirrors your actual workbooks set up, but with desensitized info and shrunk to minimal data required to demonstrate your issue.

To upload file, use "Go Advanced" button found at bottom right of Edit/Quick Reply. Then click on "Manage Attachments" hyperlink. It will open new tab/window where you can manage uploads.

**kingofcamden** · 09-26-2018, 04:11 AM

Morning CK76,

Ah, thanks so much for that. Didn't know that it actually makes a difference. I believe the Pivot is OLAP based - it pulls the data from an external source (database server) on refresh. That might explain why the code isn't working... Is it very complex to write vba for this? If yes, I might have to look into another solution...

Many thanks for your help!

**CK76** · 09-26-2018, 09:59 AM

It's not all that complex. But the difference isn't well documented.
Ex: .CurrentPage isn't available for OLAP. For OLAP based model, you can use .VisibleItemsList = Array() to filter without looping through items. etc.

Syntax for OLAP/Data model PivotFields.
[QueryName].[ColumnName].[ColumnName]

to access item in that field.
[QueryName].[ColumnName].&[ItemName]

You can use code like below to loop through each field and items in Pivot Table to check name syntax (Change sheet2 as needed).

Please Login or Register  to view this content.

But for OLAP I'd recommend using SlicerCache to control visible items.
Ex: If filtering for single item...

Please Login or Register  to view this content.

If you need further help, it would help if you can upload sample table with same data structure as your workbook and how your pivottable is structured (need not have external connection).

**kingofcamden** · 09-26-2018, 12:06 PM

Hi CK76,

Thanks for your comments and the suggestions. I tried both, but can't get it to work really. However, I now think I might use a slicer to filter based on the value. The only issue is that I currently use the dropdown value (which I would now replace with a slicer) in formulas so do you think there is a way to copy the value selected in a slicer to a cell somewhere so I can use it in a formula? Sorry, bit of a different question really!

I found this, but can't get it to work:

Please Login or Register  to view this content.

Think that would be a good workaround to what I am trying to achieve...
Thanks for your help!

**CK76** · 09-26-2018, 12:34 PM

That code is for regular pivot table. For OLAP based model... since .VisibleSlicerItemsList is array of items...

Please Login or Register  to view this content.

Change Debug.Print to something like Sheets("someSheet").Cells(i + 1, "A") = ar(i) to put the value back in Column A, starting at row 2.

**kingofcamden** · 09-27-2018, 05:19 AM

Hi CK76,

Thanks so much for this. Tried it, but doesn't seem to work (not sure what I am doing wrong). I can confirm now though, that it is indeed an OLAP based model. I cut-copied the slicer to my dashboard from a different worksheet (that contains the actual Pivot) so might this be a problem for the "thisworkbook.slicercaches" function?

Please Login or Register  to view this content.

Unfortunately I am not allowed to upload the file as it contains data I am not allowed to distribute. Hope you can still help...

Many thanks!

**CK76** · 09-27-2018, 05:24 AM

No need for sensitive data. Just use exact headers and data type, but with desensitized data. 10 rows should be enough.

**kingofcamden** · 09-27-2018, 08:05 AM

Hi CK76,

I think I found a solution! I basically just created a second slicer on the dashboard (that controls the 1st slicer on the pivot worksheet) so I can search for a specific value and filter the pivot based on that value. Good news is that the selected value can be included in a formula (as a regular cell value). So all in all: easier than I thought it would be!

Thanks again for your help

**CK76** · 09-27-2018, 09:29 AM

You are welcome and glad you got it solved.

Pivot Table to filter based on cell value on different worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Pivot Table to filter based on cell value on different worksheet

Re: Pivot Table to filter based on cell value on different worksheet

Re: Pivot Table to filter based on cell value on different worksheet

Re: Pivot Table to filter based on cell value on different worksheet

Re: Pivot Table to filter based on cell value on different worksheet

Re: Pivot Table to filter based on cell value on different worksheet

Re: Pivot Table to filter based on cell value on different worksheet

Re: Pivot Table to filter based on cell value on different worksheet

Re: Pivot Table to filter based on cell value on different worksheet

Re: Pivot Table to filter based on cell value on different worksheet

Thread Information

Users Browsing this Thread

Similar Threads

Pivot table filter based on a cell

[SOLVED] Modify a worksheet based on the pivot table filter

Change Pivot Table Filter Based on Cell Value when Pivot Table is from an external Databas

[SOLVED] Filter Pivot Table Based on Cell Value

[SOLVED] Using vba to change pivot table filter based on cell value in another worksheet

Change Pivot table Filter Based on Cell Value Multiple Filter items Possible?

Filter Pivot Table Based on Cell Value

Bookmarks

Bookmarks

Posting Permissions