What I think is awesome, though, is using Unicode Character Symbols to do fun things with drop-down lists, such as star-ratings using ★ or checkboxes using the characters √, ✔, ☐, ☑ or ☒. This means that you can't show different colors and fonts within the drop-down list. The font used in the drop-down list cannot be changed, so it is always just a black sans serif font. Check Boxes and Star Ratings with Excel Drop-Down Lists If you're thinking of getting fancy and want to define a name without a cell reference such as =, the drop-down list won't work.Īnother bit of trivia: In old versions of Excel, using a named range was the only way for a drop-down list to reference a range on a different worksheet. Note: When using a named range for a data validation list, the named range must be defined as a reference to a range of cells, or it must be a formula like OFFSET or INDIRECT or INDEX that returns a reference. Why? If you want to change the range, you only need to edit the defined name (via Formulas > Name Manager) rather than finding and editing all cells that use that particular data validation. You could use a reference for the Source field like =Sheet2!$A$1:$A$3, but I usually prefer to name the list. In the data validation dialog box, instead of entering the list manually, you enter a reference to the named range in the Source field as shown below: You can later hide the worksheet containing your list to keep your workbook looking nice and clean or to prevent a user from changing the list. In this case, I've named the range "myList". For example, let's say I have a separate worksheet with my list defined in cells A1:A3 as shown below. Instead of manually entering the list of items in the data validation dialog box, you can reference a range of cells. The more elegant approach is to use a reference to a range, or even better than that - a named range.
#Microsoft excel drop down lists update#
The problem with this approach is that if you use this in a lot of cells and later want to update the list, you have to update all cells that use the list and there is a good chance you'll miss one. This approach is great for simple Yes/No options and other lists that appear only once in your spreadsheet. Entering the Source of a Drop Down List as a Comma-Delimited List
0 kommentar(er)
