通过导入链接的2个Google Sheets工作簿-如何在一个工作簿中添加新行而不影响其他工作簿

Question

我有两本谷歌工作簿。一个是费率表，另一个是带有定价的供应商列表。

我通过importrage命令将带有定价工作簿的供应商列表链接到费率表工作簿。这非常有效，在您向费率表工作簿添加新行或删除行之前，所有内容都会按原样更新。此时，供应商工作簿不会更新引用，只是指向与原来相同的行，现在显示的数据不正确。

如何动态地让供应商列表工作簿注意到费率表工作簿中行的添加或删除，并更新导入公式？

这能做到吗？

非常感谢您的帮助。

编辑：我准备了2个样本工作簿。它们是测试表1-费率和测试表2车道

我把我们所有的价格都写在第一页。在第二种情况下，我们将有供应商通道。此工作表由多个选项卡组成，所有选项卡都有一个单独的车道，该车道链接到费率工作表中的一行。

在车道表中，我通过导入A8单元格将两者连接在一起。现在，它可以正常工作和链接，直到您在运费表中插入一条新线，在车道链接的上方，然后它不会更新，但会显示新线信息。

在费率表中插入新行时，是否有办法使车道表动态更新到下一行？

两页链接如下：

费率

车道

Answer 1

更多的信息会有帮助。

几个想法：

1. 使用命名范围而不是静态引用。例如，如果在费率表中创建一个名为MY_DATA的命名范围，该范围引用A2！C600，您可以将“我的数据”包含在供应商表中的导入中。请注意，每次添加行时仍然需要更新MY_数据

Answer 2

为了达到预期的效果，你必须为每一个速率设置一个ID。为了得到这个答案（基于你的示例表），我假设这可能是一个起源城市。在测试表2中，我添加了新表-SheetTest，其中：

第一步

我正在寻找desire City位于费率表中的行号（例如，它的Mcclellan City）。代码很简单：

MATCH(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1JD45y-0-37Kh2KsHnRAMuB0quPtMVuLKuT1qjnNcm1Q/edit","Rates!B:B"),0).

这给了我-

第二步

我使用此结果动态生成最终范围-“Rates！A10:X10”。A8单元格中的最终结果是（为了更清楚，我将工作表的地址更改为-othersheeturl-：

=IMPORTRANGE(
  "-othersheeturl-",
  "Rates!A"&
      MATCH(A2,IMPORTRANGE(-othersheeturl-","Rates!B:B"),0)
      &":X"&
      MATCH(A2,IMPORTRANGE("-othersheeturl-","Rates!B:B"),0)
)

工作示例

警告

我看到在您的原始表中，您使用了费率表中的“take”Origin City，因此我删除了A2中的依赖项，使其成为ID。我希望这能解决您的问题。

一种方法是相同的，但工具是不同的。主要目标是找到符合标准的行号——“从城市”和“到城市”。然后这个行号将被用来连接期望的范围。

为了更好地管理复杂的公式，最好将它们分成更小的部分，就像“普通”编程一样。因此，我们有：

从A5的城市

New Albany

去B5的城市

Calgary

B1中的费率表（外部表）url：

1JD45y-0-37Kh2KsHnRAMuB0quPtMVuLKuT1qjnNcm1Q

你不必把整个URL，只需要这部分

计算我们在B2中查找的行数的公式

=QUERY(
{IMPORTRANGE(B1,"A1:B"),
ArrayFormula(row(INDIRECT("A1:A"&ROWS(IMPORTRANGE(B1,"A1:B")))))},
"select Col3 where Col1='"&B5&"' and Col2='"&A5&"'
limit 1")

上述代码：

1. 从速率表（外部）构建数组-列A1： B和表示行号的虚拟索引
2. 然后，只显示第nr 3列（索引），其中Col1与我们的To City匹配，Col2与from City匹配
3. 在结束是一个限制只有一个结果（如果有更多的然后一个机器）

B3中的欲望范围

="Rates!A"&B2&":X"&B2

呈现A11中外部工作表中所需数据的最终公式

=IMPORTRANGE(B1,B3)

工作示例

1. 您可以隐藏第1-3行，也可以将上述所有公式打包成一个。这取决于你。我更喜欢第一种方法。
2. 这个解决方案给你自由，只要你想要的条件多