INDIRECT函数引用其他工作表的单元格范围 [英] INDIRECT function to reference cell ranges of other worksheets

问题描述

我正在努力推断出一种对其他工作表上的单元格区域进行动态INDIRECT引用的方法.将不胜感激任何建议，详细信息是:

I am struggling to deduce a way to make dynamic INDIRECT references to cell ranges on other worksheets. Would appreciate any suggestions, details are:

该工作簿包含4个工作表(Product1，Product2，Product3，仓库).仓库工作表包含以下公式，用于从三个产品工作表中填充每个仓库的库存清单(源自

The workbook includes 4 worksheets (Product1, Product2, Product3, Warehouses). The Warehouses sheet contains the following formula to populate an inventory list for each warehouse from the three product worksheets (derived from http://exceltactics.com/make-filtered-list-sub-arrays-excel-using-small/). This is the formula in cell B3:

=IFERROR(INDEX(INDIRECT(B$2&"!B$3:B$400"),SMALL(IF(INDIRECT(B$2&"!$C$3:$C$400")=$B$1,ROW(INDIRECT(B$2&"!B$3:B$400"))-ROW(INDIRECT(B$2&"!B$3"))+1),ROWS(Product1!$B$3:$B3))),"")

位置:

仓库-> $ B $ 1 = Warehouse1或Warehouse2

Warehouses-->$B$1 = Warehouse1 or Warehouse2

仓库-> B2，C2，D2 =产品1，产品2，产品3的列标题

Warehouses-->B2, C2, D2 = Column headers for Product1, Product2, Product3

产品表-> B列=序列号

Product Sheets-->Column B = Serial #

产品表-> C列=位置(仓库1，仓库2)

Product Sheets-->Column C = Location (Warehouse1, Warehouse2)

目前，我必须修改每行公式的最后一部分:ROWS(Product1!$B$3:$B3)，ROWS(Product2!$B$3:$B3)和ROWS(Product3!$B$3:$B3).我试图像代码的其他部分一样将其动态链接到列标题(例如，ROW(INDIRECT(B$2&"!B$3:B$400")).我之所以陷入困境，是因为范围$B3必须随每一行而变化，而其他行是静态的，并且可以用引号引起来.

Currently, I have to amend the last part of the formula for each row: ROWS(Product1!$B$3:$B3), ROWS(Product2!$B$3:$B3) , ROWS(Product3!$B$3:$B3).I am trying to dynamically link it to the column header like the other parts of the code (e.g. ROW(INDIRECT(B$2&"!B$3:B$400")). I'm stuck though because the range $B3 has to change with each row, whereas the others are static and are fine enclosed in the quotations.

这项工作很重要，因为我希望能力较弱的用户能够将公式复制到新列而不必进行修改.对此有任何感激之情！

This effort is important because I want less capable users to be able to copy the formula to new columns without having to amend it. Appreciate any thoughts on this!

推荐答案

有多种方法可以将递增数字引入 ROW函数像ROW(1:1)一样，当填写后返回 1、2、3，... .在您的情况下，k yu将从ROW(3:3)开始.

There are a number of ways to introduce an incrementing number into the INDIRECT function's string concatenation. I prefer a simple ROW function like ROW(1:1) which returns 1, 2, 3, ... when filled down. In your case,k yu would start at ROW(3:3).

... -ROW(INDIRECT(B$2&"!B"&ROW(3:3)))+1),
..., ROWS(INDIRECT(B$2&"!B3:B"&ROW(3:3))),"")

只是提醒；在用文本描述的单元格地址上保留绝对$标记对于可视化提示可能前进到下一个(相对)或否(绝对)，但表示行或列的文本是 实际上会发生变化，因此它们至少有点多余.

Just a reminder; leaving the absolute $ designations on the cell addresses that are described with text may be useful as a visual reminder as to what may advance to the next (relative) or not (absolute) but the text representing the row or column is never going to actually change so they are at least a bit superfluous.

这篇关于INDIRECT函数引用其他工作表的单元格范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！