Search results

If you've got Excel 2013 then slicers let you do that...you just have to check the option ‘Hide items with no data’. PivotTables don't let you do that natively, which is a real shame. But I'm in the process of adding some functionality via some VBA code that lets users do this. (I'm writing...

Hi Lesley. I take it you're using ActiveX dropdowns, but this may be of interest to you and possibly an easier option: http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/ http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/

By the way, a fully dynamic formula to serve up the validation lists shouldn't present me too much problem, so there will be no need to use that Product Attributes sheet at all. THat said, it might pay if you could clarify how the Master Data sheet gets populated. Automatically? Or do you (or...

It also strikes me that with a bit of clever rearrangement, you could use a PivotTable to serve up this information. THey would merely have to select the category from a slicer or PivotTable Dropdown, and all the relevant attributes for that category would appear across the top. THen you could...

Also, what actual input do they fill out once they've selected something? i.e. are they putting in quantity, or customer details, etc?

Ok. I just don't see how a single template is a good idea...especially if your product attributes vary wildly across all 650 categories. So I'm going to explore my 'one sheet per category' option just a little more before we drop it. How about you simply have a hyperlinked index in the first...

So users will only be filling out the input sheet for one category?

Can't you use a different sheet for each category?

If for some reason I couldn't use a PivotTable directly because of some formatting issue, then I'd either format my PivotTable so it didn't look like a PivotTable or I'd use GETPIVOTDATA to fetch the data from the PivotTable into whatever format the boss wants. The boss wouldn't be any the...

I would never use a SUMPRODUCT in a case like this. Just use a PivotTable. Much easier for someone else to understand, and much more efficient.

Anything volatile would recalculate the moment you switch back to auto again, so this would only make a difference if you were going to do multiple things while calc is set to manual.

My suggestion is to NOT change the column headers, but instead use something more generic. e.g. in column k you have either the heading "Material" or "Neckline" depending on the choice in B1. Why not simplify things, and just do away with that dropdown in B1 and rename the category in K1 to...

Peter - instead of using FREQUENCY, how about putting the data into a PivotTable, grouping the Pivot by the bins you need, then dragging one of the fields over to the VALUES area and changing the aggregation to COUNT?

From http://dailydoseofexcel.com/archives/2004/08/18/running-macros-when-a-workbook-is-opened/ There are two ways to run a macro when a workbook is opened; the Workbook_Open event and an Auto Open macro. The Workbook_Open event resides in the ThisWorkbook module. The Auto open macro belongs in...

...and then there's my way that uses a hidden slicer and a copy of the PivotTable masquerading as a dropdown: http://dailydoseofexcel.com/archives/2014/08/16/sync-pivots-from-dropdown/

Yeah, but the set-up is a bit of a pain.

Can you upload your VBA so we can see where you've gone wrong?

Good point. Another of my general rules is never use a SUMPRODUCT or SUMIFS etc if a PivotTable will do.

Further to what Narayn suggests, volatile formulas could be the culprit. See my blog post at http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/ But I suspect the problem is those UDFS. Mixing home-made UDFs and tables can be a recipe for disaster, as it turns out...

BY the way, SUMIFS is many, many more times efficient than SUMPRODUCT. Never use SUMPRODUCT if you can do the same thing with SUMIFS, is my golden rule. In fact I devote a whole chapter to this in the book I'm working on.

I'd suggest the approach I outlined at http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/ which is completely dynamic and doesn't require you to set up named ranges. In the coming weeks I'm going to turn my approach into a one-click setup via a macro, to support...

By the way, my code doesn't need a VBA expert to use. In fact, i'm turning it into an add-in that I can sell alongside my book.

The problem is, the PivotTable_Update event gets triggered by lots of things that have nothing to do with filtering or slicers. Which is why I'm writing my code...to identify exactly what caused an update, and if caused by a filter or slicer action, to identify which field(s) are affected. Check...

I'm in the process of finishing some code that effectively generates PivotField_Update and Slicer_Update events and then intelligently syncs any Pivots/Slicers that you want it to. This code is the combination of a whole bunch of routines of mine, and is based on some posts I've previously done...

Oftentimes this is actually the best approach. Sometimes it's much more efficient to have Excel perform these via it's highly optimised worksheet functions than hand-rolled VBA.