Search results

I think I'm going to quote that in my book, David.

Alternatively, a PivotTable is perfect for this kind of thing, if you want to eyeball the duplicate email addresses and then work out which to discard.

Craig Hatmaker recently posted a free add-in to allow functionality such as this: https://sites.google.com/site/beyondexcel/project-updates/comboboxdatavalidationadd-in

Here's a much simpler - but much more inefficient - implementation. It still uses the same data layout, but uses only a handlful of Names to accomplish the dirty work. The first three are relative names i.e. Left_3 means "Three cells to the left of here". Left_1 ='DV Tables'!D17 Left_2 ='DV...

You might also want to take a look at http://www.tushar-mehta.com/excel/software/retro_slicer/ for another approach. I initially tried this, but then found I got better results from using a Listbox-based approach, although I have had to write a LOT of code to get this to work seamlessly with...

@chirayu Good start. You might be interested in my boredom-driven solution. See http://dailydoseofexcel.com/archives/2015/11/17/filtering-pivottables-with-vba-deselect-slicers-first/ I'm still working on this, but it's almost finished and will be available commercially fairly soon.

To give you an idea of just how complicated this is, here's all the names it uses for a 4-level dropdown setup. The names Level_2, Level_3, and Level_4 are the 'final' names used by the validation dropdowns, and everything else is workings for them.

Narayan...bit of a confusing thread this one. Yes, you're correct: for a two-level cascading dropdown setup, if you had say 'Meat' and 'Wine' as the only choices in the first level, and then had sublists of 'Red' and 'White' for both of them, then you can quite happily have duplicates between...

I'll try to find time today to whip up an example and post back. Busy day for me today though...

Assuming your requirement is to set up cascading dropdowns with non unique subcategories, I know a clever - but complicated - way around this that I've used to answer a similar question on this forum before at http://forum.chandoo.org/threads/dynamic-drop-downs-with-large-table.18496/ It...

Kiki: What email program are you using?

You misunderstand me in regards to the slicers. I'm not talking about the part of the code that relates to drag-and-drop, but rather to the part of the code that syncs the filter selection across pivots. i.e. this bit of code: For Each pfMain In Target.PageFields For Each pf In...

With lots of code. I'm taking a look at it, and have made quite a bit of progress. I'm going to release a commercial add-in that does this.

Here's some ideas on how you can make this more efficient still. 1. Instead of using code to replicate any changes to filter selections across all the PivotTables, use Slicers to connect identical PivotFields across all the PivotTables. A Slicer will ensure that filter selections are always kept...

Kiki...would your real pivottable happen to have a field containing student email addresses in it? Or are those email addresses in a separate table? Also, where is the source data for the PivotTable? Is it an Excel range, or is the PivotTable connected to a database?

I might have a crack at coding something up to do this. Kiki: the code could also prompt you for a list containing the email addresses, and automatically email each of those 1000 individual worksheets to the relevant student, meaning you don't have to do anything but click one button and it's...

It still crashes on my system. I'm using Excel 2016 for PC. You say you're using Excel 2011. Is that mac?

@vletm: If I try to open your file, Excel crashes for some reason

MITChip: There are two things wrong with your sample file. firstly, you have misunderstood how to set up the master table...the names across the top must exactly match the names of things in your sublists. Secondly, your sample data contains duplicates at the Level3 and Level4 levels, which this...

Josefin: Check out my post at http://chandoo.org/wp/2014/10/18/introducing-structured-references-for-pivottables/ This will do exactly what you need.

Hi Chip. Couple of questions for you: In your sample file, there is only one choice possible for Level1, which is "One". I take it in the real world example, there will be more than one choices the user can make from the Level1 dropdown Where is the source data for the dropdowns drawn from? A...

KartikiJ: It's a bit hard to optimize your formula or suggest alternatives without a sample file that gives context. Any chance you can post one? That said, you should be able to replace this bit: NOT(ISNUMBER(MATCH(QJ$1,'Resource Leave Plan'!$A$4:$A$83,0))) ...with this...

How many columns in your dataset? Do you need all of them? My suggestion: extract a subset of the records keeping just the columns you need. And if you're going to be aggregating up dates from say daily or transactional to monthly, perhaps work out how you can pre-aggregate the data that you...

Ah yes, forgot about that. You could address that with some code fairly easily. But if this is just to get around a minor irritation then it's probably not worth it.

Here's an example. I made the shape transparent, and made the text in the shape reference the cell above, so it's completely dynamic. Then I hid the extra row.