Updating cells based selection drop down list data validation
The latter is my preferred way because it provides numerous advantages, the most essential of which are: Okay, enough theory, let's get into practice.
As usual, the first thing for you to do is to write down all the choices for your drop-down lists in a worksheet.
To create a new name that references the table header, select it and then either click .
Now that your source data is ready, it's time to set up named references that will dynamically retrieve the correct list from your table.
In sheet 1 I have a database of sorted foodstuffs (Column A) and their nutritional content in the following columns (proteins(B), carbohydrates(C), fats(D), calories(E)) In sheet 2 I have created a data validation rule in the column A, the criteria is "list from a range" and looks in the first column in Sheet1 to create a drop down list with the selected foodstuff. But when I select any given foodstuff in the drop down list in sheet 2, I would like the corresponding cells to update with relevant nutritional data from sheet 1.
I have tried to find a way to find the address of the cell that data validation list is referring to, and thus be able to use OFFSET or a similar function to find the relevant cell data.
This time, you will have to employ Excel tables to store the source data.
Let me remind you that tables were introduced in Excel 2007, so you can use this method in modern versions of Excel 2016, 2013, 20.