Validating a cell value excel macro
Using the named range technique does allow you to change the name of the Table or Column.
The reference in the named range will automatically be updated.
A shortcut to typing the name of our range is , which brings up a list of any ranges we’ve named.
We can simply click on the range we want in order to select it.
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.
Since multiple cells could be using this reference, it makes maintenance easier if you forget to press the “Apply these changes to other cells…” checkbox.
The drop-down button will still appear next to the cell, but you won't be able to click it.Then access the Data Validation window by selecting the Data tab on the ribbon and clicking on the Data Validation button.In the Data Validation window, under the Settings tab, we can type the name of our range into the Source field.There you can highlight the selection that you want to use for your range.
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.