Select E2:E15-these are the existing cells you want to fill using the validation control.Now you're ready to create the validation list, as follows: In the resulting dialog, check Top Row (if necessary), and Excel will use the header text to name the range Shelf_Code_List ( Figure C).In the Defined Names group, click the Create From Selection option.
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: There's one more step before you can build the validation list. In the resulting dialog, check or uncheck the My Table Has Headers option ( Figure B), and click OK.įigure B Specify whether your table has headers.
If you're working with your own data, you can convert a data range into a Table as follows: These two data sets are Table objects in the demonstration file. Figure A We'll use these two Table objects to build a dynamic validation list.
How to do a vlookup in excel 2016 on mac code#
We'll add the validation control to column E and use it to enter the appropriate shelf code for each record. We have a data set in columns B through E and a list of unique shelf code values. If both the data set and the list source are Table objects, Excel updates everything as you work.įigure A shows a simple sheet with two Table objects. In its simplest form, a validation list isn't dynamic, but combining the feature with the Table object changes that. And while that may sound silly, a misspelled value returns erroneous results, whether you're filtering or using complex functions to analyze your data. You can't keep a user from choosing the wrong value, but at least it'll be spelled right. You'll want to use these lists to ease data input and to prevent errors. SEE: 30 things you should never do in Microsoft Office (free TechRepublic PDF) A dynamic validation listĪ validation list lets you limit users to specific values. 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. 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. For your convenience, you can download the demonstration. I'm using Excel 2016 (desktop), but both techniques will work in earlier versions. Excel is still a security headache after 30 years because of this one feature.
How to do a vlookup in excel 2016 on mac how to#