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

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

You can avoid the spare column heading rows which you are inserting for successive DSUM() entries
Suppose there is a three column database in C3:E10  (headings are in row 3).
Say headings are Name, Department and Amount
Copy C3:E3 to C13:E13
In cell C14, enter any name from range C4:C10
In cell D14, enter any department from range D4:D10
In cell C15, enter any name from range C4:C10
In cell D15, enter any department from range D4:D10
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

Related: Way to Update & Download SONY SVE14A2X1EH Wireless State Device driver,How to Update & Download SONY SVS1511X9RB Graphics Driver for D2 (Intel) v.9.17 -,Method to Download SONY VGN-CR290N Realtek High Definition Audio Driver v.,Download SONY VGN-FS710/W Utilities DLL Update v. driver,Method to Update & Download SONY VGN-NR460E/P Intel Graphics Media Accelerator Driver v.,How to Fix Error 0x0000c1f5 SoluciĆ³n?,Error 0x80071a91 Win7 Fix Patch,Error 0x800ccc60 Windows Live Mail [Solved],Error Message 0x800ccc79 Outlook 2000 Fix,What is Error 0xc1ab0001?,[Answered] x64 saplugin dll,How to Fix Problem - Kb943729 Download?,Troubleshooting: Bccode D1 Error,Java Runtime Tech Support,Troubleshooting: ERROR NO SUCH GROUP Error,Solution to ashoutxt.dll Missing Error,Solution to cxcore096.dll Missing Error,Where can I Find dsauth.dll File,How to Fix msde.dll Not Found Error,Best Method to Fix psizewizres.dll Error
Read More: [Solved] Document Displays Differently on Other Computers,Solution to Error: double click while in protection mode?,Fast Solution to Problem: Do all updaters use IE to update?,Solution to Error: DJ Inkers will not work on Windows 7. Is there a way to make it work?,How to Fix Error - DOS environment?,Data validation question. Maintaining and updating named ranges.,Desktop Gadget Gallery on Windows 7,delete empty rows,Deleting a page and also how can I copy an exact page lay out on a new page,Date Formula for 1st Sunday of each Month (3rd to 9th)

No comments:

Post a Comment