如何在Google表格中创建多个工作表引用的INDIRECT数组字符串? [英] How to create INDIRECT array string of multiple sheet references in Google Sheets?

问题描述

我正在尝试使用查询来显示多个Google表格中的数据.我每周都会制作一张新的工作表，其中有一个特定的工作表名称，例如Week of 01/13，Week of 01/06等.

I am attempting to use a query to display data off multiple Google Sheets. I make a new sheet every week that has a specific sheet name, e.g. Week of 01/13, Week of 01/06 and so forth.

以下是产生我的想法以供参考的地方:

The following is where my idea spawned from for reference:

我有一个使用COUNTA(INDIRECT("'" & A5 & "'!E4:E",true)

I have a summary sheet that is using COUNTA(INDIRECT("'" & A5 & "'!E4:E",true)

A5是一个单元格，该单元格将日期和单词连接起来以复制 工作表名称.

A5 being a cell that concatenates a date and words to replicate the sheet names.

直到B5<=today()

因此我可以将其设置为忘记"，工作表将继续 给我我每周的数据，以保持日子的进展，并保持床单清洁 直到一周到来.

So I am able to set it an forget it and the sheet will continue to give me my weekly data as the days progress and keeps the sheets clean until the week is upon us.

长话短说，我有一个查询，可以使用特定的参数为我提供所有需要的数据，但我每周必须使用新的工作表名称手动更新数据语法数组.

Long story short, I have a query that I use that gives me all the data I need with a specific parameter but I have to manually update the data syntax array with the new sheet names each week.

=QUERY({'Week of 01/13'!A:P;'Week of 01/06'!A:P;'Week of 12/30'!A:P;'Week of 12/23'!A:P;'WEEK OF 12/16'!A:P;'WEEK OF 12/09'!A:P;'WEEK OF 12/02'!A:P;'WEEK OF 11/25'!A:P;'WEEK OF 11/18'!A:P;'WEEK OF 11/11'!A:P;'WEEK OF 11/04'!A:P;'WEEK OF 10/28'!A:P;'WEEK OF 10/21'!A:P;'WEEK OF 10/14'!A:P;'WEEK OF 10/07'!A:P;'WEEK OF 09/30'!A:P;'WEEK OF 09/23'!A:P;'WEEK OF 09/16'!A:P;'WEEK OF 09/09'!A:P;'WEEK OF 09/02'!A:P},
 "Select * where Col11 = 'RD' order by Col2 desc",0)

我想建立对一个数组的引用，该数组将根据日期自动填充一个串联.

I would like to build a reference to an array that will auto-populate a concatenation based on the day.

使用以下代码，我可以将其连接起来，给我所需的数组，

Using the following code I can have the concatenate give me the array I need,

=if(H4<=today(),CONCATENATE("'",H$1,text(H4,"mm/dd"),"'!A:P;",),"")

但是当我尝试将其输入到查询函数中时，它只是返回连接的文本:

but when I try to input it into the query function it just returns the concatenated text:

=QUERY(I1,"Select *")

 

'Week of 01/06'!A:P;'Week of 01/13'!A:P

我尝试使用大括号和不使用大括号都没有成功.

I have tried with and without the curly brackets with no success.

我希望工作表能够刷新，并确保它是正确的一天，新工作表的名称已填充，查询已更新.

I would like the sheet to be able to refresh and see that it is the correct day, the new sheet name is populated and the query gets updated.

在使I1正常工作时，我需要帮助.

I need help with making I1 work.

链接到测试查询表

推荐答案

如果您尝试更新查询所查看的数据，并且正在为它提供一个字符串，则需要将该字符串放入indirect( ) 功能.这样会将您的字符串解释为数据引用，并将您的query()指向正确的方向.

If you're trying to update the data your query is looking at and you're feeding it a string, you need to put that string within the indirect() function. That will interpret your string as a data reference and point your query() in the right direction.

为此，您可能会拥有

=QUERY(INDIRECT(I1),"Select *")

这篇关于如何在Google表格中创建多个工作表引用的INDIRECT数组字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！