A
amariepsu
See the attached pdf for a step by step instruction on creating a drop down box using excel 2007.
T
tamale
Amariepsu,
Thanks a lot, I used your instructions this morning
Any more fun tricks, please feel free to post them!
Tamale
Thanks a lot, I used your instructions this morning
Any more fun tricks, please feel free to post them!
Tamale
H
Hodgepodge
Create and Use a Dynamic Range in a Drop Down Box
Create a Dynamic Range
You can use a dynamic formula to define a range. As new items are added, the range will automatically expand.
Note: Dynamic named ranges will not appear in the Name Box dropdown list. However, you can type the name in the Name Box, to select the range on the worksheet.
To use the Dynamic range as a Data Validation List:
1. Select the cells (usually a column) to you want to add the validation rules to.
2. On the Data Tab, select Data Validation.
3. On the Settings Tab of the pop up, select “List”.
4. In the SourceEnter the formula for the Dynamic Range, or enter the name of your dynamic range (ex: =EmployeeList)
=OFFSET(‘Sheet1!$AA$2,0,0,COUNTA(‘Sheet1’!$AA$2:$AA$65536),1)
Amariepsu,
Thanks a lot, I used your instructions this morning
Any more fun tricks, please feel free to post them!
Tamale
Create a Dynamic Range
You can use a dynamic formula to define a range. As new items are added, the range will automatically expand.
Note: Dynamic named ranges will not appear in the Name Box dropdown list. However, you can type the name in the Name Box, to select the range on the worksheet.
- On the Formulas Tab, select Define Name. Type a name for the range, e.g. EmployeeList
- In the Refers To box, enter an Offset formula that defines the range size, based on the number of items in the column, e.g.:
=OFFSET(Sheet1!$AA$2,0,0,COUNTA(Sheet1!$AA1:$AA65536),1)
In this example, the list is on Sheet1, starting in cell AA2
The arguments used in this Offset function are:- Reference cell: Sheet1!$AA$2
- Rows to offset: 0
- Columns to offset: 0
- Number of Rows: COUNTA(Sheet1!$AA1:$AA65536)
- Number of Columns: 1
Note: for a dynamic number of columns, replace the 1 with:
COUNTA(Sheet1!$1:$1)
To use the Dynamic range as a Data Validation List:
1. Select the cells (usually a column) to you want to add the validation rules to.
2. On the Data Tab, select Data Validation.
3. On the Settings Tab of the pop up, select “List”.
4. In the SourceEnter the formula for the Dynamic Range, or enter the name of your dynamic range (ex: =EmployeeList)
=OFFSET(‘Sheet1!$AA$2,0,0,COUNTA(‘Sheet1’!$AA$2:$AA$65536),1)
Similar threads
