The choices for Fruits are in a block of cells directly below a heading ( FRUITS).
#CREATING A DROPDOWN IN EXCEL FOR MAC HOW TO#
How to Setup a Dynamic Range in ExcelĪ normal (static) range name refers to a specified range of cells ($H$3:$H$10 in our example, see below):īut a dynamic range is defined using a formula (see below, taken from a separate spreadsheet which uses dynamic range names):īefore we get started, make sure you download our Excel example file (sort macros have been disabled). To do this, you use a formula, rather than a fixed range of cell addresses, to define the named range. A dynamic range name is one that automatically expands (or contracts) to exactly match the size of a block of data as entries are added or removed.
Likewise if, for example, the Pears and Strawberries entries are erased, they will no longer appear in the dropdown, but instead the dropdown will include two “empty” choices since the dropdown still references the entire FruitChoices range, including the empty cells H9 and H10.įor these reasons, when using a normal named range as the list source for a dropdown, the named range itself must be edited to include more or fewer cells if entries are added or deleted from the list.Ī solution to this problem is to use a dynamic range name as the source for the dropdown choices. Since the named range (FruitChoices, in our example) refers to a fixed range of cells ($H$3:$H$10 as shown), if more choices are added to the cells H11 or below, they will not show up in the dropdown (since those cells are not part of the FruitChoices range). This second method makes it easier to edit the choices in the list, but adding or removing items can be problematic.
0 kommentar(er)
