Search results

@Faseeh Can you repost a link to the finished plot? The dropbox link above doesn't seem to be working, but I'd like to take a look at this.

If you ever iterate over PivotItems, set the PivotTable's .ManualUpdate to TRUE while you make your changes, and set it back to FALSE afterwards to avoid the PivotTable refreshing after each and every change. This will radically speed your code up. If filtering on just one item, put hat field...

You can use the DataModel to add comments to PivotTables if you have Excel 2013 or later. See https://stackoverflow.com/questions/45132111/add-column-with-custom-text-to-pivot-table/45137050#45137050

Of course, if Chandoo registers as a New Zealander, I'm screwed.

You and me, head to head. Let the trans-Tasman rivalry begin!

I am working on a commercial add-in that does just this. Screenshots at the following StackOverflow links: http://stackoverflow.com/questions/38904866/excel-pivot-table-filters-how-to-make-one-filter-drop-down-dependent-on-anothe/38911606#38911606...

Hi TyWils. No, I haven't (yet) progressed this, because I've been working on some other PivotTable-based addins that I want to get out the door first. But this is on my radar, and I would be interested in talking further about what it is you are trying to achieve, because there's nothing like a...

Read this post: http://www.contextures.com/excel-pivot-table-sorting.html

Are all these PivotTables based on the same raw data? If so, then if you have Excel 2010 or later you can just add a Slicer for the field concerned, and connect that Slicer to all the other PivotTables. Any changes you make in one field are then automatically replicated in all the others.

@NARAYANK991 Note that one offset formula alone can cause issues, if it is at the head of a large-enough dependency tree, as per my article at http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/

You can use vba to do this as per my guest blog post at http://chandoo.org/wp/2014/10/18/introducing-structured-references-for-pivottables/

Late contribution, but if you ensure that the outline column is entered a text (and not general format) AND each sub part of the outline has the same amount of digits in it, then you can sort on it, as outlined at this post...

This kind of thing indeed takes a lot of work. I've been working on an 'improved' slicer for over a year, that only appears when you double-click a PivotTable, and that lets you do all sort of wildcard filtering that you can't do with native Slicers/Pivots:

@Manosh Here's a guest post I wrote some time back on the Chandoo forum where the named ranges are updated automatically as per your comment above. http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/

YoungSparky: having formulas that reference other workbooks is usually a recipe for disaster. In this case, it won't actually work unless both workbooks are open, because INDIRECT can't retrieve data from closed workbooks. Nor will INDEX, which is another function I often use to make cascading...

I'm working on an add-in that is getting close to completion that will allow this. Might be a month away, possibly two. PM me if you're interested. Likely to cost US$100 per licence, and I'm also looking at ways to licence it to a particular project (i.e. file) at a cheaper rate.

For this challenge, I'd probably use the CHOOSE function combined with an INDEX/MATCH combo to feed it the right choices, and some Excel Tables to keep everything dynamic. Here's how the formula that feeds the DV would look...

I wrote some guest posts over at CHandoo's blog some time back that might be of interest. The first shows how to use VBA to reset downstream choices if the upstream ones change: http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/ The second shows how to stop users changing...

What does that formula do?

Yeah, it's easy to overlook that, which is why I asked for a screenshot. Can you click the Data tab and let me know whether or not there is an area of the ribbon called Get and Transform: I would prefer you take a screen shot of this and post it here.

Cool. So just to confirm, under the 'Subscription Product' heading it says "Microsoft Office 365 ProPlus". It's that ProPlus bit that determines if you have PowerPivot (and possibly PowerQuery) installed.

Okay, I understand that. I'm just trying to understand conceptually how this data fits together, and that intermediate table is how a PivotTable will 'see' the data even if we can avoid creating it. (Or we simply might be able to automate the creation of that intermediate table from the separate...

Secondly, you want to append actuals tothat table by turning this: ...into this: ...with those Home and Visitor rows being filled by matching the date in the Raw Actual data to the dates in the Raw Budget file. Is that correct?

Okay, still struggling a bit to comprehend your requirements, but it seems to me like you want two things. Firstly, you want to automatically turn this: ...into this: Is that correct? That is called unpivoting a crosstab, and I can probably help you out there, if I make some tweaks to some...

Okay, I'm taking a look at this now. But I probably won't get to it tonight...there is a mountain of dinner dishes to do. If you were in Wellington, New Zealand I'd make you come over here and do them!