Log In
Register
Home
New Member
May 10, 2020
Offline
Somehow I am failing to get the relevant answer from searching the web. Maybe due to how I phrased the question.
The environment consists of the following:
1. Separate XL book that holds the data - approx. 40 thousand lines. Let's assume only 4 columns, Transaction Type (only 2 options income, expense), Customer/Vendor (any), Invoice Nr (which contains the customer/vendor name as a prefix) and an amount column.
2. Separate workbook with a Pivot table, with the transaction type, Customer/Vendor and Invoice Nr as page fields.
3. The requirement. When the transaction Pagefield is selected as Income, then in the Page field only the customer names must be visible for selection and not the vendors. Also when the customer is selected, then only the relevant invoice nrs, with the customer prefix should appear in the dropdown list of the pagefield.
4. With a normal Pivot, I can loop through the Pivot items using the "For each PtItem in PivotTable.PiviotFields(2).PivotItems" convention, but failing to make only the relevant items visible in the dropdown. Then decided to explore the Power Pivot option.
5. With PowerPivot the system gives an error when trying to loop through the Pivot item fields, for example, the Invoice nr. Using the following convention: Pf.PivotField.PivotItems where Pf.PivotField is set to the given page field
Would like to loop through all the items and based on a given condition(s) assign the relevant values to an array, which represent the valid items to be displayed and assign this array to the visible list of the lower pagefield using the method ".VisibleItemsList".
6. Is there a way to create a filter on the Power Pivot column filter of the data in vba, as if you would do it manually using the table properties?
Your guidance or direction to the relevant vba code snip or to a given forum/link would be highly appreciated.
Thank you in advance.
1 Guest(s)
