I have a google sheets document which contains two sets of data in separate worksheets:
- Public IP addresses
- 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:
- A public IP can be mapped to a single private IP
- 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 220.127.116.11 18.104.22.168 22.214.171.124 None 126.96.36.199 188.8.131.52 184.108.40.206
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?