I have a name range of cells on a seperate worksheet in a workbook. I need
the range to dynamically change size so that only users can add values to the
column or remove values from the column and the choicelist will still contain
all values. The choice list dropdown is on sheet1 and the values are in a
named range on sheet3. suggestions on how to do this?

Also, the solution needs to work in office 2003 and 2007.

"hshayh0rn" wrote:

I have a name range of cells on a seperate worksheet in a workbook. I need
the range to dynamically change size so that only users can add values to the
column or remove values from the column and the choicelist will still contain
all values. The choice list dropdown is on sheet1 and the values are in a
named range on sheet3. suggestions on how to do this?

http://peltiertech.com/Excel/Charts/...umnChart1.html

discusses how to create a dynamic chart. You use the same procedure for
your dynamic range.

HTH,
Barb Reinhardt

"hshayh0rn" wrote:

I have a name range of cells on a seperate worksheet in a workbook. I need
the range to dynamically change size so that only users can add values to the
column or remove values from the column and the choicelist will still contain
all values. The choice list dropdown is on sheet1 and the values are in a
named range on sheet3. suggestions on how to do this?

Just make sure your dynamic range is a workbook range, not a worksheet range,
or you won't be able to use it with DataValidation.

"hshayh0rn" wrote:

I have a name range of cells on a seperate worksheet in a workbook. I need
the range to dynamically change size so that only users can add values to the
column or remove values from the column and the choicelist will still contain
all values. The choice list dropdown is on sheet1 and the values are in a
named range on sheet3. suggestions on how to do this?

when i use the formula on thta page my dropdown stops working. the formula is
accepted but when i click on the drop down it doesn't open up and show me any
values.

"Barb Reinhardt" wrote:

http://peltiertech.com/Excel/Charts/...umnChart1.html

discusses how to create a dynamic chart. You use the same procedure for
your dynamic range.

HTH,
Barb Reinhardt

"hshayh0rn" wrote:

I have a name range of cells on a seperate worksheet in a workbook. I need
the range to dynamically change size so that only users can add values to the
column or remove values from the column and the choicelist will still contain
all values. The choice list dropdown is on sheet1 and the values are in a
named range on sheet3. suggestions on how to do this?

Forgive my ignorance, how do I do that? I'm adding the formula to the "refers
to" field within the defines name box.

"Barb Reinhardt" wrote:

Just make sure your dynamic range is a workbook range, not a worksheet range,
or you won't be able to use it with DataValidation.

"hshayh0rn" wrote:

I have a name range of cells on a seperate worksheet in a workbook. I need
the range to dynamically change size so that only users can add values to the
column or remove values from the column and the choicelist will still contain
all values. The choice list dropdown is on sheet1 and the values are in a
named range on sheet3. suggestions on how to do this?