Data Validation Dropdown OR manual entry specific text

[EdwardSurrey]

Hello

I have the following Data Validation formula that creates a dependent list dropdown. It's complicated because it's designed to remove blank spaces.

=OFFSET(Backup!$AK$1,1,MATCH($C$9,Backup!$AK$1:$EC$1,0)-1,COUNTIF(OFFSET(Backup!$AK$1,1,MATCH($C$9,Backup!$AK$1:$EC$1,0)-1,100,1),"?*"),1)

I also want to allow the manual entry of "INSERT SITE", regardless of whether that exists in the dropdown.

I tried the following:

=OR(C10="INSERT SITE",OFFSET(Backup!$AK$1,1,MATCH($C$9,Backup!$AK$1:$EC$1,0)-1,COUNTIF(OFFSET(Backup!$AK$1,1,MATCH($C$9,Backup!$AK$1:$EC$1,0)-1,100,1),"?*"),1))

But I get an error "The list source must be a delimited list, or a refence to single row or column"

Any ideas?

Thanks

 

[BenMcBen]

As far as I know in data validation - if you have "Allow:" set to "List", then you need to supply a list with all items that are valid, but if you use "custom" you are free to build formulas to define what is allowable, but there is no "in-cell dropdown" option for this. So you need to add "INSERT SITE" to your list if you want a drop down...

 

[EdwardSurrey]

As far as I know in data validation - if you have "Allow:" set to "List", then you need to supply a list with all items that are valid, but if you use "custom" you are free to build formulas to define what is allowable, but there is no "in-cell dropdown" option for this. So you need to add "INSERT SITE" to your list if you want a drop down...

Thanks Ben.

The issue is, the dropdown is dependent on another cell where the user selects a country. If the user changes that country dropdown, I want this dependent dropdown to default to "INSERT SITE" through a VBA script. It's fine, unless the user deletes the country cell data. I think the VBA would then throw up an error because of the validation.

 

[mikerickson]

Add "INSERT SITE" as the first cell in the list of countries.

 

[EdwardSurrey]

Add "INSERT SITE" as the first cell in the list of countries.

The thing is, this is a dependent dropdown

Dropdown 1 = Country
Dropdown 2 = Site.

If someone clears the country from Dropdown 1, I don't think Dropdown 2 works at all.

 

[mikerickson]

The thing is, this is a dependent dropdown

Dropdown 1 = Country
Dropdown 2 = Site.

If someone clears the country from Dropdown 1, I don't think Dropdown 2 works at all.

Or add it as first of each list of sites.

 

[EdwardSurrey]

Or add it as first of each list of sites.

Yeah but the site list is dependent on the country choosen. If a user hits DELETE on the Country cell, Dropdown 2 simply will not work, regardless of whether I have "SELECT SITE" listed under any of the site lists.

 

[Akuini]

If the user changes that country dropdown, I want this dependent dropdown to default to "INSERT SITE" through a VBA script.

I'll try to write a macro to do that.
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.

 

[EdwardSurrey]

I'll try to write a macro to do that.
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.

Thanks Akuini. I didn't notice you had replied! I actually managed to solve this