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

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?

1 Answer 1


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


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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.