User input data setup box

[dsrt16]

I want to create a setup form where users can create items that will go into a named range used in data validation lists.

I know how to create a user input form to get it to populate into one certain cell, but not in an undetermined range, like I need here.

I have already created all the named dynamic lists. Now the user will just populate it by filling out a form.

Example: One of the dynamic named lists is called material options. It starts in cell P9 and can go up to cell P100. I used offset and counta to create the named list since I don't know how many entries the user will have.

Now on the setup page, I want to have a user input form asking them to list all the different kinds of materials they edit. Then this will populate into the dynamic material list.

So a user might enter into the form websites, novels, nonfiction books, resumes, dissertations. And then cell P9:P13 will auto populate with those answers.

A different user might have only 3 materials they edit, so then they would only auto populate into cell P9:P11.

 

[dsrt16]

I got it to work.

I reverted back to the original code (with ThisWorkbook.Sheets).

The problem was the named formula. I forgot the )1 after the $N$99.

Whew! Thanks for your help!

 

[dsrt16]

AND it worked twice. I tested it two times and it worked perfectly.

I then went in to test it again along with all the other setup items, and it gave me the error again: application-defined or object-error defined error. Highlighting the Address.range line.

 

[dsrt16]

I figured it out again!

I had to have data in the dynamic named range. Then whatever the user entered in the form replaced the data. I didn't have anything in there as I was waiting for user input. SO for now I put in dummy values in cell N10 and N11 and N12, and then whatever user enters in form replaces it.

Thanks!

 

[ParamRay]

Ok glad you got it working. Yes, it wouldn't work without data already in the range, because in this case excel can't resolve the dynamic range when it's empty. Have fun.