Kiến thức

Multiple Column Data Validation Lists in Excel-How To-PakAccountants.com

Bạn đang xem: Multiple Column Data Validation Lists in Excel-How To-PakAccountants.com

Multiple Column Data Validation Lists in Excel – How To

Facebook

Twitter

WhatsApp

Pinterest

Today we are learning how to make dynamic multi columnar data validation lists in Excel that helps display much more data in the drop down list and are far superior and flexible than usual data validated lists. Here is what we will achieve by the end of this tutorial (left) and what we usually get with usual data validation lists (right)

A

simple data validation list

helps us display specific data range in the form of drop-down list in a desired cell.

A while back we learnt how to make

dynamic data validation lists

based on Excel tables that grow as the base data grow. It is much easier approach to data validation lists without the hassles of using OFFSET function coupled with unnecessary bits with more limitations still. And I implemented the same idea in designing

Invoice template

. However, it had single column data list and today we will be updating the same template with multi-column data validation list.

And this will be our final result once again:

Multi Column Data validation List – Step by step

Step 1:

Download the excel workbook

that if you intend to work along and open it in Excel.

In this workbook we already have the dummy data for customers and products. And few other techniques applied for invoice to work flawlessly. For the purpose of this tutorial I have simply removed the simple data validation list that we will update with a better solution.

To learn how this template was originally made and different techniques applied check this tutorial: 

FREE Excel Invoice Template V1.0 with Customer and Product list – Unlocked + Download ready!

Step 2: Go to customers worksheet and name the cell B1 CustomerName by typing it in the name box and pressing enter.

Step 3: Have an active cell inside the table in Customers worksheet and go to design tab > change the name to CustomersTable.

Step 4: Go to Formulas tab > in defined names group click Name manager > click Define Name. A new dialogue box will appear. Make the changes as following:

  1. Name: CusTab
  2. Refers to: =CustomersTable (same name that we gave to table in Step 3 above)

Step 5: Go to formula tab again > click define name and make the following input:

Name: CusTabFix
Refers to: =CusTab

What in the Name you are doing!
Now you must be confused that why we have defined the name twice for a table that already gets a name by default.

For some weird reasons the combo box that we will use to make multi-column data validation list does not take named ranges based on table names even if it refers to a table. That is why I wrapped a table name inside a named range and then wrap it again under another name to work around this problem.

Step 6: If you don’t already have the developer tab enabled then follow the steps as shown following:

Step 4: Go to developer tab > In controls group click Insert drop down button > click combo box under active X control. Cursor will change to let you draw the combo box. Point to the cell where you want it and draw it by clicking and holding left mouse button and releasing it once done:

Step 5: Right click on inserted combo box and click properties. A long dialogue box will open with lots of properties.

Step 6: Few of the important changes that I made in properties:

  1. Name: CustomersList
  2. Border color: Menu bar or Orange
  3. Border style: 0
  4. ColumnCount: 4
  5. ColumnWidths: 100 pt;80 pt;80 pt;100 pt [defines how wide each column should be in the list]
  6. Drop button style: 2
  7. Linked cell: CustomerName
  8. ListFillRange: CusTabFix
  9. ListWidth: 390 pt

This is how properties look after the changes:

And this is how it performed after the changes:

Step 7: Now that we have the list working, we can fetch the contact details of customers in the box under combo box using a formula in cell H10 of Invoice worksheet:

=IFERROR(VLOOKUP(CustomerName,CustomersTable,5,FALSE),””)

Remember CustomerName is the name we gave to cell above customer table. This cell is also used as linked cell in combo box properties and it is where we get the output of combo box. And it changes if we select a different name from the list.

Awesome!

Hope you have a good use of this technique in your work as it adds much more understanding to the user while making selections from the list.

Facebook

Twitter

WhatsApp

Pinterest

Previous article

Turn off Excel Table Formulas / Structured references

Next article

All Excel Accounting and Bookkeeping Solution – Template

Hasaan Fazal

Teaching professional business subjects to the students of FIA,

ACCA

, CIMA, CA etc. He also found

ACCA LIVE

which is Pakistan’s first portal to provide

online classes

and distance learning solutions to FIA/ACCA students. At PakAccountants.com he is busy making study material for different qualifications. Beside writing articles he answers questions asked using

ASK TUTOR!

Chuyên mục: Kiến thức

Related Articles

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

Check Also
Close
Back to top button