So the MATCH function will return the value of 9. Since the first 8 entries in the list are not blank, the first 8 values in the array will be FALSE, and the ninth value will be TRUE (since the 9 th row in the range is empty). The MATCH function returns the position of the first TRUE value, within the array that’s returned by the INDEX function. The next piece of the formula is the MATCH function: =OFFSET(FruitsHeading,1,0,IFERROR( MATCH(TRUE,the above,0)-1,20),1) But setting the row and column inputs to zero (as is done here) causes INDEX to return an array containing the entire block of data.
INDEX is normally used to pick a certain value (or range of values) out of a block of data, by specifying a certain row and column (within that block). The INDEX function returns an array containing the 20 TRUE / FALSE values created by the ISBLANK function. The next piece of the formula is the INDEX function: =OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE, INDEX(the above,0,0),0)-1,20),1)Īgain, “the above” refers to the ISBLANK and OFFSET functions described above. In this example, the first 8 values in the set will be FALSE since the first 8 cells are not empty and the last 12 values will be TRUE. ISBLANK then creates a set of 20 TRUE and FALSE values, indicating whether each of the individual cells in the 20-row range referenced by the OFFSET function is blank (empty) or not. But the ISBLANK function is operating on the 20-row range of cells that the OFFSET function defines. Here, the OFFSET function (explained above) has been replaced with “the above” (to make things easier to read). The next piece of the formula is the ISBLANK function: =OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX( ISBLANK(the above),0,0),0)-1,20),1) So that gives us the 20-row block where the Fruits choices are entered. This OFFSET function basically says: Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is 20 rows long and 1 column wide. This references the block of 20 cells (underneath the FruitsHeading cell) where choices may be entered. The “innermost” piece is OFFSET(FruitsHeading,1,0,20,1).
Now let’s break down the formula into pieces (color-coding each piece), to understand how it works: =OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK( OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1) The number 20 refers to the entire block where entries can be made, not to the actual number of entries. Note that in this example, there are only 8 entries in the list, but there are also empty cells below these where additional entries could be added. The number 20 (used two times in the formula) is the maximum size (number of rows) for the list (this can be adjusted as desired). The entire formula used to define the dynamic range for the Fruits choices is: =OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)įruitsHeading refers to the heading that is one row above the first entry in the list. That heading is also assigned a name: FruitsHeading: The choices for Fruits are in a block of cells directly below a heading ( FRUITS).
#Make excel cells static office 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)
