• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. jeffreyweir

    violin plots in Excel

    @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.
  2. jeffreyweir

    Select next Item in SlicerCaches for pivot table

    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...
  3. jeffreyweir

    Data Entry and Pivot Table

    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
  4. jeffreyweir

    Excel World Champ !!!

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

    Excel World Champ !!!

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

    [2013] Pivot Table Slicer - display only select values in the slicer

    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...
  7. jeffreyweir

    Data Entry and Pivot Table

    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...
  8. jeffreyweir

    Pivot Problem

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

    change multiple fields in pivot when one cell changes

    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.
  10. jeffreyweir

    Spreadsheet Slowing Down!

    @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/
  11. jeffreyweir

    Make a Pivot Table a named range

    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/
  12. jeffreyweir

    Sorting an Outline in form #.#.#

    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...
  13. jeffreyweir

    Convert Slicer into Button

    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:
  14. jeffreyweir

    Context sensitive lists using Data Validation

    @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/
  15. jeffreyweir

    Need help with dependent drop down list between workbooks using named ranges

    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...
  16. jeffreyweir

    Pivot table from SQL source with same filter or slicer for all sheets

    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.
  17. jeffreyweir

    offset and (maybe) indirect

    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...
  18. jeffreyweir

    clear dependent data validation cells

    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...
  19. jeffreyweir

    Reduce capacity of calculating

    What does that formula do?
  20. jeffreyweir

    Pivot Table: Actual v. Budget Across Multiple Events

    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.
  21. jeffreyweir

    Pivot Table: Actual v. Budget Across Multiple Events

    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.
  22. jeffreyweir

    Pivot Table: Actual v. Budget Across Multiple Events

    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...
  23. jeffreyweir

    Pivot Table: Actual v. Budget Across Multiple Events

    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?
  24. jeffreyweir

    Pivot Table: Actual v. Budget Across Multiple Events

    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...
  25. jeffreyweir

    Pivot Table: Actual v. Budget Across Multiple Events

    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!