Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

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.

1 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.

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

share|improve this answer

Your Answer

 
discard

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