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 you have read our privacy policy.