Link a Drop Down to a Pivot Table - Dynamic Excel List

📗 Get Your Video Workbook Template Here: »» 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 drop-down 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 drop-down 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 re-link it to more cells so i've put on the three different types of drop-down 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 🅾 cutt.ly/Up4ExcelSub
======================================================================================
💥 Get a Shortcuts Cheat Sheet: »» www.up4excel.co.uk/shortcuts
🔓🔑 Remove Excel File Open Passwords: »» • Excel File Locked? Lea...
🎁 Your small gift will help me make better videos for you and others.
Thank You: www.paypal.me/Up4Excel

[@Up4Excel]

I'm offering personal coaching to only 2 people. Get details now ml.up4excel.com/htcapp04 - Offer Ends 19th August 2022

[@Up4Excel]

📗 Get Your Video Workbook Template Here: »» 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.

[@Up4Excel]

👉 Up4Excel Downloads Library: »» ml.up4excel.com/library ✅ FREE Access to ALL Up4Excel Files. Includes Excel Templates, Training Workbooks, Example Data, Cheat Sheets and more. New Content Added Weekly!

[@wayneedmondson1065]

Nice one John! Thanks for sharing :)) Thumbs up!!

[@Up4Excel]

Thank you too Wayne!

[@parmeetrai1982]

Hi, I always watch your videos and it is always helpful. Could you please help me develop a functionality where I can switch between 4 different calculated fields(sum of cost, sum of hours, sum of forecast cost, sum of forecast hours) with a drop down list. Like if I select from list the sum of cost the pivot table should show only sum of cost. Hope to hear back soon. This will save my job. Thanks!

[@Up4Excel]

The typical way to do something like that would be to structure your data so all the values are in one column and there is another column with the value type (i.e. hours, forecast cost etc). You then add a slicer (or drop down list) on the value type column. Hope that gives you some ideas 👍 John

[@orins2052]

Thank you. All really great but the accent! 🙂

[@Up4Excel]

Well not a lot I can do about my accent... but glad you found the video helpful 👍 Thanks for the comment.

[@orins2052]

@@Up4Excel Thank you again. Not only it's exactly what I was searching for, I have also found other interesting stuff here, so I subscribed too. 🙂

[@Up4Excel]

@@orins2052 Great stuff, hope you find a lot more good stuff here in future too 👍