Glide Training Logo

How to create a variable drop down list in Excel

  

Written by Jane Hames

Creating a variable drop down down list using Indirect

During some advanced Excel training, I was asked how to create a variable drop down list in excel using a data validation list that changes depending on the value in a related cell. The reason for the question was so that estimators entering building supplies into a data list could enter the name of the material and then select from a list of quantities that that specific material can be ordered in. Always up for a challenge, I decided to use my creativity and I found a solution through the combined use of data validation and the indirect function.

There are a few steps to the process, which will need to be carried out in order. I have two sheets, one called lists which contains a list of the items that will be ordered and the quantities that those items can be ordered in:
named ranges

The second sheet is called ordering sheet and this is the sheet where the item to be ordered will be entered and next to the item will be the appropriate drop down list:
named ranges

  1. Using named ranges, name cells to form the drop down list of quantities, as the item name. In the example below, I have named B2:D2 as Plasterboard, B3:C3 are named Bricks, B4 is named Panels
    named ranges
  2. Use data validation on the Ordering sheet to create a basic drop down list for the items.
    data validation
  3. On the ordering sheet, use data validation to create a drop down list using indirect to look for the matching range name. (The indirect function looks for an area in the workbook with the same name as the cell refered to in the data validation.) See the image below:
    data validation and indirect function

Note:this is using some of the more advanced Excel techniques and a prior knowledge of naming ranges, data validation and functions is required

 


previous post: Remove date formatting from cells in Excel

next post: How To Prohibit Weekends Using Data Validation

Why choose Glide?

Our Unique Approach

Our Unique Approach

We work on a bespoke, requirements-led basis rather than offer a set range of courses. We will work with you to identify your specific requirements and objectives before any training is delivered. That way you can be sure that training will always deliver exactly what you're looking to achieve.

View details »

Our Low Prices

Our Low Prices

We deliver all our on-site training at our customers' premises. Not only does that avoid any travel inconvenience for your team, but also it means we don't have the overheads required to maintain a dedicated training centre - and in turn we are able to pass these savings on to you.

View details »

Our Expertise

Our Expertise

Our trainers have designed and delivered IT training for a wide range of organisations and people including accountants, lawyers, data analysts, sales consultants and buyers. Clients benefit from our our ability to find creative solutions to their problems based on many years of experience.

View details »

Balderton Logo
Kreston Reeves Logo
National Institute of Health Research
England Rugby
Caravan and Motorhome Club Logo
Bird and Blend Logo
Ropetackle Logo
RBLI Logo
Farnborough Airport Logo
Kroll Logo
The Apprenticeship College Logo
Shoreham Port Logo
Adur and Worthing Councils Logo
Balderton Logo
Kreston Reeves Logo
National Institute of Health Research
England Rugby
Caravan and Motorhome Club Logo
Bird and Blend Logo
Ropetackle Logo
RBLI Logo
Farnborough Airport Logo
Kroll Logo
The Apprenticeship College Logo
Shoreham Port Logo
Adur and Worthing Councils Logo