Google Spreadsheets Data Validation - Unique Values

Question

I have a google sheets document which contains two sets of data in separate worksheets:

1. Public IP addresses
2. Private IP addresses

In my private IPs worksheet I want to create a dropdown which lists values from the public IP addresses worksheet. (Basically representing a private to public IP mapping.)

However there are some rules I need to follow:

1. A public IP can be mapped to a single private IP
2. I need to be able to enter a custom value that's not in the worksheet OR I need to be able to use one value repeatedly for example the public IP may be "None"

So if you were looking at the list it might look like:

 Public IP     Private IP
 None          1.1.1.1
 20.20.20.1    1.1.1.2
 None          1.1.1.3
 20.20.20.5    1.1.1.4

The data validation function seems to be able to do this - sort of. It can display a list of values from another worksheet and you can enter values not on the worksheet. But what I can't seem to figure out how to do is to only display unused unique values in the dropdown.

Is there a way to do this?

Answer 1

Perhaps the =Unique(A1;A2) function might be helpful to you?

http://woorkup.com/2010/02/19/10-useful-google-spreadsheet-formulas-you-must-know/

Otherwise there was this script from a previous answer on another SE site.

https://webapps.stackexchange.com/a/21699