How to create two advanced dynamic lists in Excel
The article Five ways to take advantage of Excel list features showed five basic list features built right into Excel. You don’t have to do much to take advantage of them, but sometimes you’ll need something more sophisticated.
In this article, I’ll show you two advanced list features using a validation list and a lookup function to generate a dynamic list. Neither technique is superior—your needs will dictate your choice.
The VLOOKUP() function is available in earlier menu versions, but both techniques rely on the Table object to be dynamic, and the menu versions don’t support the Table object.
The browser edition supports validation lists and Table objects, but inserting new data into a Table is awkward—that edition ignores the Tab wrap to the new record behavior when entering a new record.
Microsoft Office 365 Key : Install office 365 with Product Key
A Dynamic Validation List:
A validation list lets you limit users to specific values. You’ll want to use these lists to ease data input and to prevent errors. You can’t keep a user from choosing the wrong value, but at least it’ll be spelled right.
And while that may sound silly, a misspelled value returns erroneous results, whether you’re filtering or using complex functions to analyze your data.
These two data sets are Table objects in the demonstration file. If you’re working with your own data, you can convert a data range into a Table as follows:
Click anywhere inside the data set.
Click the Insert tab.
In the Tables group, click Table.
In the resulting dialog, check or uncheck the My Table Has Headers option , and click OK.
Office 365 Product key : Install Office
Specify whether your table has headers:
There’s one more step before you can build the validation list. You can’t specify a Table object as the source for a validation list, but you can if you give the Table a range name, as follows:
Select H2:H7 :
Click the Formulas tab.
In the Defined Names group, click the Create From Selection option.
In the resulting dialog, check Top Row
Give the list a range name.
Now you’re ready to create the validation list, as follows:
these are the existing cells you want to fill using the validation control.
Click the Data tab.
In the Data Tools group, choose Data Validation from the Data Validation dropdown.
From the Allow dropdown, choose List.
In the Source control, enter =Shelf_Code_List
Reference the named range you gave the list source:
With the validation list in place, you can start filling the cells in column E with the appropriate shelf code values. In addition, you can enter each manually by choosing the appropriate code from the dropdown.
For more details, visit : https://www.office.com/