在Google工作簿之间导入命名范围数据 [英] Import named range data between Google Workbooks

问题描述

如果在同一Google Sheets Workbook中，则公式:index(nameRangeRow，namedRangeColumn)可以完美地将位于命名行和命名列的交点处的数据片段抽取"或提取到SAME Workbook中的另一张Sheet上.

当我要从不同的工作簿"中提取数据时，如何编写公式以获得相同的效果?我假设它是importRange，但是我似乎无法获得该公式来识别两个命名范围并在其交集处找到数据.

公式:importRange("key"，"namedRangeRow")效果很好，但是它提取了目标工作簿范围内的所有数据.因此，我认为这是该公式的某种变体，只是找不到在importRange函数中表达两个范围的正确方法.

解决方案

下图中的公式从单独的Google电子表格(即工作簿)中两个命名范围的交点处的单元格中提取一个值. (链接到表.). >

数据所在的工作表如下. (链接到工作表.)

SetLinkData工作表是设置列表"工作簿中的第二个工作表，如下图所示，其中包含歌曲目录"工作表的ID.

If within the same Google Sheets Workbook, the formula: index(namedRangeRow , namedRangeColumn) works beautifully to "pull" or extract the piece of data residing at the intersection of the named row and named column onto another Sheet in the SAME Workbook.

How do I write a formula to get the same effect when I want to pull data from a DIFFERENT Workbook? I presume it is importRange, but I can't seem to get that formula to recognize two named ranges and find the data at the intersection thereof.

The formula: importRange("key", "namedRangeRow") works great, but it pulls all the data in the target Workbook range. So, I presume it's some variation of that formula, I just can't find the right way to express the two ranges within the importRange function.

解决方案

The formula in the image below pulls a value in a cell at the intersection of two named ranges from a separate Google spreadsheet (i.e., workbook). (Link to sheet.)

The sheet where the data resides is below. (Link to sheet.)

The SetLinkData sheet is the 2nd sheet in the Set List workbook as in the pic below and contains the ID of the Song Catalog sheet.

这篇关于在Google工作簿之间导入命名范围数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！