Drop down list macro?
 

I have a sheet set up that uses columns B through L. A macro is set up to
insert an extra row keeping the format at the end of the list when a command
button is clicked. I have a drop down list in column C formatted for the
list. How or what can I put in the macro that would regenerate that drop
down list in column C on the new row?

--
Rob

Drop down list macro?
 

soteman wrote:
I have a sheet set up that uses columns B through L. A macro is set up to
insert an extra row keeping the format at the end of the list when a command
button is clicked. I have a drop down list in column C formatted for the
list. How or what can I put in the macro that would regenerate that drop
down list in column C on the new row?

--
Rob

Hi Rob,

You could make your drop down list a dynamic named range. To do this
you could go Insert|Name...|Define...|type a suitable name in the
"Names in workbook:" box (eg "DropDownList1" w/o speech marks)|type the
following formula into the "Refers to:" box
"=OFFSET(Sheet1!$C$1,1,0,COUNTA(Sheet1!$C:$C)-1)" w/o speech
marks|Click the "Add" button|OK.

The formula in the "Refers to:" box assumes that your drop down list
has a heading in C1 that is not to be a part of the list. If C1 is not
a heading and is a part of the list then change the formula to
"=OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$C)) ". Also, edit the Sheet
name in the formula if your list is not on Sheet1.

In the "Source:" box on the Data Validation dialog use "=DropDownList1"
w/o speech marks.

Ken Johnson