Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

migrated from superuser.com Oct 18 '13 at 6:27

This question came from our site for computer enthusiasts and power users.

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


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


share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.