根据Google电子表格下拉列表中的选择更新单元格值 [英] Updating cell values based on selection in drop down list in google spreadsheet

问题描述

我有2张数据.

在工作表1中，我有一个分类食品数据库(A列)，其以下各列的营养成分(蛋白质(B)，碳水化合物(C)，脂肪(D)，卡路里(E))

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))

在工作表2中，我在A列中创建了一个数据验证规则，条件是范围内的列表"，并在Sheet1的第一列中查找以创建包含所选食品的下拉列表.

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.

这一切都很好，花花公子.但是，当我在工作表2的下拉列表中选择任何给定的食物时，我希望相应的单元格用工作表1中的相关营养数据进行更新.

This is all nice and dandy. 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.

我试图找到一种方法来查找数据验证列表所引用的单元格的地址，因此可以使用OFFSET或类似的功能来查找相关的单元格数据.似乎ADDRESS函数的地址仅提供给我所选单元格的地址，而不提供所选单元格所引用的地址.

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. It seems the ADDRESS function address only gives me the address of the selected cell, and not the address that the selected cell refers to.

如何根据下拉列表中的选择，用工作表1中的相关数据填充工作表2中的数据?

How can I populate the data in sheet 2, with the relevant data from sheet 1, based on the selection in the drop down list?

推荐答案

假设您的列已标记，请在B2中尝试:

Assuming your columns are labelled, please try in B2:

=vlookup($A2,Sheet1!$A:$E,column(),0)  

反复复制以适合需要.

这篇关于根据Google电子表格下拉列表中的选择更新单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！