Get Your Video Workbook Template Here: »» https://cutt.ly/up4v2006MFD
Let your pivot table do all the work to populate your dropdown lists, so you don't have to. All you need to do is click refresh! Your dropdown list is populated by pivot table.

======================================================================================
Video Timings:
00:00 Overview and Demonstration
01:32 The Secret Technique
02:51 Create the Dynamic List
04:36 Link Lists to Drop Downs
05:20 Link Active X Combobox
09:38 Dealing with Multiple Pivot Tables
11:14 Method Summary

======================================================================================
#Up4Excel
#Up4ExcelTips
#Up4ExcelAdvanced

======================================================================================
there's a pivot table that happens to show some customers sales and profits for various different uh regions for example and what i wanted to do was link a dropdown list to this that could be for data entry or for producing some other function on the spreadsheet like maybe charts dashboards that kind of thing and i wanted this to happen dynamically because the thing about dropdown lists is you link them to a range of cells and then that cell list changes and you drop down this doesn't unless you relink it to more cells so i've put on the three different types of dropdown lists data validation lists form control and activex controls so if you don't know what those are don't worry i've got another video that talks you through all of those and why you might want to use one versus another pros and cons etc etc link in the description for sure may even be on your screen right now if you want to look at it so anyway what i've done is this list here shows everybody in this pivot table when i change the list up here we now get a different list of names which represents what's in the pivot table at the moment very useful in d

so have i done it well i've used a little uh helper so we just need to unhide this row one what's our little helper an item count so all i've done is said count how many items there are in the pivot table and the minus one is just because i've got a grand total at the bottom of the pivot table so simple count a which counts text count straight count count numbers only minus one okay so we know there's 33 in that list for example when i click on there that goes down to 17. do i use that right well what i do is i use that to create a dynamic named range and then i link that dynamic name range oh you if i use that dynamic name range as the named range on all of those different types of drop down lists so i'm going to show you how to do that right now because it's a couple of subtleties depending on the different drop down that you might be using

so first off see the named range so we're going to formulas name manager here's my name range customers and if i click on that you can see it's highlighting that list right now so what is it well just simply using an offset formula which starts at the top of the pivot table on the header row of the particular column that you might be interested in in the pivot table always use the header row in my view because it doesn't matter so much in pivot tables but in any other list but you know people can insert rows between the header and the first item of data so by sticking with the header and moving down one row which is the next column the next uh number head move down one row you're always going to pick up the first item even if somebody's inserted rows in the spreadsheet or anything like that so start the header move down one don't move columns at all how many rows do i want in my range well i'm just going to pick up this number at the top which is this count that we'll put on how many columns do you want we just want the one column so it's a relatively relatively straightforward i've never seen offset before perhaps it it isn't so straightforward but if you have relatively straightforward formula for defining a range k thing being t

======================================================================================
Here at Up4Excel we’re on a mission to help YOU:
» Get your Excel skills UP and your task time DOWN
» Focus on shortcuts and fast impressive results
» Improve your productivity and free up your time

Everyone will assume you work 24 hours a day to produce the kind of output you’ll be producing in no time…. with the help of Up4Excel training.

You get new video releases every week, packed full of ways to save time and impress those around you.
Don't miss out and fall behind.....
SUBSCRIBE NOW https://cutt.ly/Up4ExcelSub

======================================================================================
Get a Shortcuts Cheat Sheet: »» http://www.up4excel.co.uk/shortcuts
Remove Excel File Open Passwords: »»    • Excel File Locked? Learn How to Break...  
Your small gift will help me make better videos for you and others.
Thank You: https://www.paypal.me/Up4Excel