从列表中引用工作表名称从其他工作表返回单元格值 [英] Returning cell values from other sheets by referencing sheet names from a list

问题描述

我有一张电子表格，大约有二十张不同的表格，由股票行情命名。因为这些是模板，所以每个工作表中的信息都在相同的单元格中。例如，本年度的EPS总是在单元格A55中，而不管表格。

I have a spreadsheet with approx twenty different sheets named by stock ticker. Because these are templates, the information in each sheet is found in the same cells. For example, the EPS for the current year is always in cell A55 regardless of the sheet.

我想构建一个摘要表，将根据代码从模板中提取数据。因此，设想一个摘要表，其中列出了列A中的列表（列表名称）和列B中的公式，以引用列表中每个不同表格中的相同位置。

I want to build a summary sheet that will pull data from the templates based on the tickers. So envision a summary sheet with tickers (also sheet names) down column A and formulas in columns B to reference the same position in each different sheet in the list.

我知道我可以建立一行，然后简单地复制它，并为每个工作表名称/代码执行查找/替换，但似乎我应该能够自动化公式，所以这些知道查看列A，然后转到表该名称，然后返回公式中引用的数据。随着时间的推移，这将使得更容易添加纸张/报价单。我已经尝试了= INDIRECT没有成功。

I know I can build out one row and then simply copy it down and do Find/Replace for each sheet name/ticker, but it seems I should be able to automate the formulas so these know to look at column A, then go to the sheet with that name, then return the data that is referenced in the formula. This would make it much easier to add sheets/tickers as time went on. I have tried =INDIRECT without success.

如何获取公式以引用摘要表中的列A，以便这些知道在哪个模板/表中查找信息？

How do I get the formulas to reference column A in the summary sheet so that these know in which template/sheet to look up the information?

推荐答案

请尝试：

=INDIRECT(A1&"!A55")

这篇关于从列表中引用工作表名称从其他工作表返回单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！