Validating a cell value excel macro
The formula uses structured reference Table formulas, and looks like the following.You can type the formula directly in the source box in the Data Validation window.Just make sure the Table name and column name are correct.I presented this technique in my article on dependent drop-down lists.If you’ve never worked with data validation lists before, I suggest you start with this tutorial for creating drop-down lists in cells before moving on.In today's post, I want to show you how to make your drop-down list dynamic.In other words, your list can automatically be updated with new options when you add or subtract entries to your source range. The Create Table window will appear, showing the range of cells that will be in your Table.Since our column begins with a header (“Products”), we want to make sure the checkbox that says “My table has headers” is checked.
Once you have defined your range, you can click OK, and then close the Name Manager window.I like to prefix my ranges with “rng” to make them easier to find in formulas. The “Refers to” field allows you to select the range that you want to include.The up arrow icon to the right of that field takes you to the worksheet.You can download the file I’m using in the video to practice on your own.Dynamic Data Validation Lists (22.1 KB)Data Validation lists are drop-down lists in a cell that make it easy for users to input data.
Then access the Data Validation window by selecting the Data tab on the ribbon and clicking on the Data Validation button.