I'm trying to create a Sheets that defines various objects, and these objects sometimes reference each other (equivalent to SQL foreign key).

Is there a way that I can limit the values of a cell, to only those values that already exist in a range of another sheet?

So say I have a sheet of parents, and a sheet of kids. Each parent is a row in the sheet, and the first column is the name. In the kids sheet, the first column is a name, and the second column is mom. I want mom to be a dropdown of the names that exist in the parents sheet.

How can this be done?


Yes, with data validation:

WA53034 example

Eg Select ColumnB of Kids, Data (top right in image), Validation..., Cell range: Kids!B:B, Critera: List from a range Parents!A:A, select from options, Save.

This should fill the range with the grey down arrows and when clicking one of these a dropdown list such as that shown should appear.

If you were able to separate the mothers from the fathers (not divorce!) you should be able to apply a list of mothers only to one column and of fathers only to another, should you so wish.

| improve this answer | |
  • Thanks this works. What if I have multiple ranges? (IE there's a person drop down, and I want it to be choosable from both kids and parents names)? – tbkn23 Feb 20 '14 at 7:48
  • No, not something that complicated, just two lists you can choose from. – tbkn23 Feb 23 '14 at 9:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.