Google Spreadsheet vlookup in validation drop-down

Question

I want to operate with ids, and not with data that related to id.

For example:

Sheet details has this data:

  | A           | B        |    
1 | category_id | category |
2 | 0           | Web      |
3 | 1           | Mobile   |

Sheet John Cena has his data:

  | A              | B           | C                   |
1 | name           | category_id | experience_level    |
2 | Angular1.x     | 0           | 10                  |
3 | Apache Cordova | 1           | 5                   |

The problem is with validation step.

From UX perspective I want a developer to use validation drop-down so he can select a category_id value by text that is in details!category, but value that was placed should be a category_id. It should be so, so I could change the category text. For example when changing text Web to Web framework I want all sheets that where relating to category automatically updated with new text.

That was a first part. The next part, ideally, that when value is 0, cell should show value from category. This we operate with category_id through category text.

I search a lot, and got overwhelmed by amount of information about spreadsheet. So need some help from SO audience, if possible.

So the question is:

Can validation custom user formula produce a drop-down list? If yes, I need to feed a range of values for validation as result of that formala by using VLOOKUP. If no I need to create custom scenarios.

Answer 1

I'm not 100% sure I understood the question correctly, but I'll try to answer them.

To your first question, the only way to do this is if you have a separate column that has a VLOOKUP for the catergory_id. This way if you modify the values in 'detail', all of the values will update.

To your second question, for Data Validation one of the options for criteria is List from a Range. You can populate a given range however you want and if you select that range for data validation the dropdown will contain those values. This will update if the criteria range updates.