一起使用IMPORTRANGE，INDIRECT和CONCATENATE [英] Using IMPORTRANGE, INDIRECT, and CONCATENATE together

问题描述

我有一张纸，它可以将数字从几个不同的纸中提取到合并数字.每周，都会在源文件中添加一个新表，所有表都具有相同的名称.我想通过更改一个单元而不是多个单元来更新合并表.当该标签位于同一Google表格中时，使用INDIRECT即可轻松完成此操作.

I have a sheet that pulls numbers from several different sheets to amalgamate numbers. Each week, a new sheet is added to the source files, all with the same name. I'd like to update the amalgamated sheet by changing one cell instead of many. When the tab is located in the same Google sheet, this is easily done with INDIRECT.

现在，合并表中的公式为:

Right now, the formula in the amalgamated sheet is:

=IMPORTRANGE(M4, "Aug29!$F$2")

该公式位于每一行中，可从多个不同的工作表中提取:

That formula is on each line to pull from several different sheets:

=IMPORTRANGE(M5, "Aug29!$F$2")
=IMPORTRANGE(M6, "Aug29!$F$2")

，依此类推.

每个星期，"Aug29"更改为新日期-"Sep5"，"Sep12"等.

Each week, the "Aug29" changes to the new date - "Sep5", "Sep12", etc.

我想做的是使用INDIRECT来(从单元格Z1中)提取等式的那一部分，这样我就不必更新每个公式.

What I'd like to do is use INDIRECT to pull that part of the equation in (from cell Z1) so that I don't have to update each formula.

我当时想我可以使用CONCATENATE通过INDIRECT创建公式的"Aug29！$ F $ 2"部分:

I was thinking I could use CONCATENATE to create the "Aug29!$F$2" portion of the formula with INDIRECT:

=IMPORTRANGE(M4, CONCATENATE(INDIRECT(Z1), "!$F$2"))

问题是，IMPORTRANGE公式需要在范围字符串周围加上引号，但我不知道如何添加它们.

The problem is, the IMPORTRANGE formula requires quotations around the range string, and I can't figure out how to add them.

我敢肯定，这很奇怪，但是想知道是否有人可以解决?

Weird problem, I'm sure, but wondering if there's anyone with a solution?

推荐答案

简短答案

使用 = IMPORTRANGE(M4，CONCATENATE(Z1，``！$ F $ 2''))或 = IMPORTRANGE(M4，Z1&``！$ F $ 2'')

INDIRECT需要一个表示单元格或范围引用的字符串，例如"Z1" ，但这会不必要地增加公式的复杂性.

INDIRECT requires a string that represents a cell or range references, like "Z1" but it will increase unnecessarily the complexity of your formula.

如果Z1具有工作表的名称，则只需使用CONCATENATE，CONCAT或&

If Z1 has the name of the sheet, then just concatenate it by using CONCATENATE, CONCAT or &

这篇关于一起使用IMPORTRANGE，INDIRECT和CONCATENATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！