Download Template: »» https://cutt.ly/up4v2012MFD'>https://cutt.ly/up4v2012MFD
If you want to look unprofessional then leave the blank rows in your dropdown lists. Otherwise, learn this technique and be proud of your dropdown lists learn to remove the blanks and keep them tidy.

Shortcuts Cheat Sheet: »» http://www.up4excel.co.uk/shortcuts'>http://www.up4excel.co.uk/shortcuts

======================================================================================
Video Timings:

00:00 Demonstration
00:19 Sheet & Data Setup
02:24 Dropdown List Setup
06:09 Prevent List Fill Range Error
06:52 Setup for VBA Code
09:46 Writing the VBA Code

======================================================================================
How do you take a standard dropdown list like this full of blanks and turn it into one where all the blanks have gone and not only that when you change the list of customers it dynamically updates to include the new list also with no blanks [Music] okay so I’m going to create a new sheet here why don't we call it from scratch so let's take this sales data here in fact let's just copy all of that like that now I’m going to also show control f1 to show my ribbon so that you can see which buttons are being pushed etc first off I had a little formula in here which picks up the customer based off of whatever's in this cell so I’ll show you how to do this it's set not important for the drop down list but it will make it look like the original here I can't remember what we had something like that so I put this as customers or something like that anyway and centered it there and then this one I just had a number in it saying which column I want to pick up so what are all I want to do here I’m going to go through this really quickly because this video is about drop down list not creating uh formulas but I’m going to use the if formula to check if there's any sales in a particular column and the column I’m going to check is the one up here so I’m going to say if offset I just allows us to move between columns dynamically so we're going to start with that starting b5 we don't want to move any rows and the amount of columns across we want to move it's just whatever number we've put in there fix that so if that is um blank give me a blank otherwise give me the customer name right now if I drag that down there you can see it's picking up customers wherever that there's a number same with express air same with regular air okay so that's how I set that up right drop down list the important bit [Music] right developer tab broken record again haven't got the developer tab click on here customize the ribbon more commands customize the ribbon tick or untick probably tick developer tab if you can't do that you need to talk to your a system administrator because it's been disabled right let's give ourselves a bit of width right in design mode we are going to insert an active x control now I’ll just show you how you might do a standard one so I held down alt there by the way when I drew that so it clicks into the grid lines and then I’m going to click properties and there's a few almost standard things that I change if you're using one of these combo box inside a worksheet and you're using it to select items to drive other data so the first one is we want it as a list rather than a combo that stops people entering you know anything they like right so we can rely on the output of this list being something um that has been chosen special effects sunken that's just annoying it's all right on a user form but on a worksheet what it does is restricts the amount of space you've got to put your font in all right talking of which your font size generally speaking you're going to need to knock one off of it because whatever the spreadsheet you know if the spreadsheet standard is 11 you need to probably go down to 10 to ensure that you're not cutting off the tails of things.

======================================================================================
#Up4Excel
#Up4ExcelVBA
#Up4ExcelAdvanced

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

======================================================================================
Download Template: »» https://cutt.ly/up4v2012MFD'>https://cutt.ly/up4v2012MFD
Shortcuts Cheat Sheet: »» http://www.up4excel.co.uk/shortcuts'>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