WMV to AVI Converter: Tech Support: Display Report Filter(s) of Pivot Table on UserForm / ComboBox

Thursday, September 29, 2016

Tech Support: Display Report Filter(s) of Pivot Table on UserForm / ComboBox

Display Report Filter(s) of Pivot Table on UserForm / ComboBox

Application
A report of 50 rows from fairly large table (250,000 rows x 50 cols).
User can select report filters for seven fields.
The underlying database is hidden.

Current solution - DSUM
The values in the report are derived with 'DSUM'.
The user selects filters from ComboBoxes on User Form.
Each of the seven ComboBoxes is populated with unique list for the respected column.
This approach has two main drawbacks:

The 'DSUM' function requires criteria consisting of two rows (label and condition), which must be contiguous.
This requirement forced me to add an extra raw for each report row, and to hide them for production.
(I don't want to specify criteria on different
sheet).The burden of producing unique list for each field.
I use a pivot table to extract the unique value lists and add an 'ALL' item.
Thanks to Chandoo for the technique.
Question 1: Is there any workaround for the two contiguous rows requirement?

Desired Solution - GETPIVOTDATA
Since I already have a Pivot Table, why not use 'GETPIVOTDATA' for the report values.
This approach eliminates the need for the extra rows necessary to specify 'DSUM' criteria.
Building the reports becomes much simpler without hidden rows.
Also, Pivot
Tables automatically creates unique list of values (including 'ALL') for each Report Filter.
Question 2: How do I populate ComboBoxes on User Form from the Pivot Table Report Filters? (User is not supposed to see the Pivot Table page or interact with it directly)
Question 3: How do I pass the selected item in ComboBox back to Report Filter.

Note: Looking for fully automated process with only unavoidable manual steps.
The above describes scenario is just a part of a process which will eventually be used to produce 20 different reports with frequent data refresh.

Keys to the Problem Display Report Filter(s) of Pivot Table on UserForm / ComboBox

Download SmartPCFixer for Free Now

Hi,

You can avoid the spare column heading rows which you are inserting for successive DSUM() entries

1.
Suppose there is a three column database in C3:E10 (headings are in row 3).
Say headings are Name, Department and Amount
2.
Copy C3:E3 to C13:E13
4.
In cell C14, enter any name from range C4:C10
5.
In cell D14, enter any department from range D4:D10
6.
In cell C15, enter any name from range C4:C10
7.
In cell D15, enter any department from range D4:D10
8.
In cell E14, you enter =DSUM($C$3:$E$10,E$13,C$13:D14) and copy down to E15

In cell E15, your problem is that the value inclused the one obtained in cell E14 as well. So the small change you have to make here is as follows. In cell E14, your formula should instead be

=DSUM($C$3:$E$10,E$13,C$13:D14)-SUM(E$13:E13) and now copy this down.

Hope this helps.

Cleaning your computer, components, and peripherals help keep everything in good working condition and helps prevent germs from spreading. You can't image how dirty the inside of your computer case can get. All the dust and dirt is going to prevent proper air flow and may even prevent the fan from working.

Recommended Method to Fix the Problem: Display Report Filter(s) of Pivot Table on UserForm / ComboBox:

How to Fix Display Report Filter(s) of Pivot Table on UserForm / ComboBox with SmartPCFixer?

1. You can Download SmartPCFixer here. Install it on your system. When you open it, it will perform a scan.

2. After the scan is done, you can see the errors and problems need to be fixed. Click Fix All.

3. When the Fixing part is done, your computer has been speeded up and the errors have been fixed

WMV to AVI Converter