Dynamic Validation list

[Smoulg28]

Hi all
I am using the built in validation function with a list. But I want to be able to add an "Other" option where if "other" is selected then the user can input a new string in to this cell. I guess adding the new string to the list would be easiest... but is not essential.
I am pretty sure that the validation | list is hard wired so could something be done in VBA to dynamically allow this validation option?
Any sugestions would be appreciated

 

[SkipVought]

Hi,

1. Your list needs to by dymanically named. Insert/Name/Define - select a name for your list to enter in the Names in workbook textbox and in the refers to textbox enter

=OFFSET(

then select the first cell in your list -- and it will look something like this...

=OFFSET(Sheet1!$A$1

then enter

=OFFSET(Sheet1!$A$1,0,0,counta(

then select the COLUMN that your list is in -- looks like this...

=OFFSET(Sheet1!$A$1,0,0,counta(Sheet1!$A:$A

then finish with...

=OFFSET(Sheet1!$A$1,0,0,counta(Sheet1!$A:$A),1)

CAVEAT: there can be NOTHING else in this column except a heading, in which case the first reference is to A2 rather than A1.

Then us THAT name in the validation list textbox -- ie if your list name is MyList then

=MyList

See: faq68-1331 How can I rename a table as it changes size



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[KenWright]

If I read you correctly then you are using the list as a prompt, but are quite happy for any other entries to be put in, but it won't allow it. You can do this however.

If you ensure that a single space is included in the list range, and that the range is named eg MyList (You cannot just use the range reference in the DV dialog box), and then in the DV dialog box type =MyList where you would normally have the range, and finally ensure that 'Ignore Blank' is checked, then they can enter what they like or use your prompt list.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Smoulg28]

Thanks Skip... works a treat.
I made a slight modification... I left a blank cell at the top and added 1 to the COUNTA part... "=OFFSET(Sheet1!$F$1,0,0,1+COUNTA(Sheet1!$F:$F),1)".
So the user can pick from the list or type in anything if required. I can have the list hidden away and add to it when required.
Cheers