Must Advanced Filter be used with Pivot Table Slicers in order to auto copy and paste data
UPDATE: Apparently all I had to do was manually go back in to the table and add the filter.
I found, what I think is, the solution to weeks of research, testing and work BUT there is one thing that I am hoping to change.
I have a large excel spreadsheet (2500+ rows x 200+ columns) where I keep detailed information on every product we carry (no sales information - strictly product information: UPC codes, package sizes available, dimensions, pricing, etc.). I have been looking for a way to more easily share the information with users that want a very abbreviated version of the spreadsheet for quick reference. After trying multiple methods that served the purpose well but, based on the quantity of formulas, it would bring work to a complete halt, I found a method using a pivot table, slicers, a macro button and the advanced filter, that did the job beautifully - not only did it present the abbreviated information, but it allows the data to be manipulated without changing anything on the master spreadsheet. However, I don't like that the advanced filter overrides the auto-filter on the excel table where the information is all being generated from. I am constantly adding and updating information in the table and use the auto-filter extensively. I am wondering if there is a way to accomplish the same end result as with the pivot table/slicers/macro/advanced filter but without the advanced filter?
I would post the link to the site where I found the tutorial but, I am unclear if that is against the forum rules for this site...
Essentially what I ended up with was 3 tabs: one had the item information table, another had the pivot table report filters and the last one presented the abbreviated information. It had two slicers where you would pick the supplier name and the brand of the product, once you picked those you clicked the macro button and the chosen, associated information was populated below. The information that you saw was copy and pasted as values, according to the column heading (they were labeled the same as the headings from the table).
Overall, this has been the best method I have found, the calculations have not interfered with the usability of the workbook - there is no noticeable delay - it's just the darned advanced filter that is throwing a wrench in the "perfect solution". Any help would be greatly appreciated.
Please accept my apologies if I have done or said anything contrary to forum rules, I am very new to all of this.
Last edited by LuScannon; 03-07-2017 at 02:08 PM.
Reason: Problem Solved
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By Anisusa in forum Excel Charting & Pivots
Last Post: 01-15-2017, 03:44 AM
By retzlar in forum Excel Charting & Pivots
Last Post: 09-26-2015, 05:11 AM
By Geoff. in forum Excel Programming / VBA / Macros
Last Post: 07-30-2015, 02:27 PM
By shelly2 in forum Excel Charting & Pivots
Last Post: 01-22-2014, 07:08 PM
By MacroNerd in forum Excel Programming / VBA / Macros
Last Post: 12-05-2013, 06:26 PM
By limalf in forum Excel Programming / VBA / Macros
Last Post: 10-17-2013, 07:31 PM
By Xcelguy in forum Excel Charting & Pivots
Last Post: 05-24-2013, 03:58 PM
Tags for this Thread
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1