When using Excel there are often occasions when you wish to provide a user with the option vary what they see – some examples are:
- Being able to select and pull up details for a particular product
- Being able to select and pull up details for a particular Customer
- Being able to select and pull up details for a particular time period
Excel requires the input to be identical to how the particular item is represented elsewhere in the spreadsheet. If its not identical then its likely that an error will be returned or that the wrong data will be provided. e.g. a month could be shown as Jan 16, January 2016, Month 1, Period 1, P1 etc.
To get round this you should use a drop down box or an in-cell data validation list selection. This will be linked to a particular range of cells which are used to provide the selections. The cells can be given a range name (see range names for more detail) In some cases this range of cells will vary in length as you add, for example, new products or new customers and ideally the range name needs to automatically change in size as you add items to the list. Fortunately, Excel has the ability to do this and its called a dynamic range
To set up a Dynamic Range Names (advanced)
To make a range name dynamic in the defines range dialogue box (highlighted in red in the example on normal ranges above) you replace the standard range (e.g. =Sheet2!$G$5:$G$16) with an offset formula such as =OFFSET(Sheet2!$G$5,,,COUNTA(Sheet2!$G$5:$G$50)). Note the COUNTA() part of this is providing the informationon the length of the list. For this to work properly its important to ensure that there are no gaps in the list it refers to.
Dynamic ranges are particularly useful where you are downloading data on a regular basis (e.g. sales orders) and the number of entries in the list will vary on each download occasion
Dynamic ranges can be used in the same way as names referring to a fixed range of cells.
Excel Monkey 