I'm trying to use formula to copy data from one worksheet to another where a column matches a certain value.
我试图使用公式将数据从一个工作表复制到另一个列匹配某个值的工作表。
To be more specific, I want Working!A2:E100 to contain Data!A2:E100 but only for those rows where Data!C2:100 contains the value "Fixed".
更具体地说，我想工作!A2:包含数据高达!A2:E100，但只针对那些有数据的行!C2:100包含值“Fixed”。
Doing this with leaving blank rows is simple, I just create the formula:
用空行来做这个操作很简单，我只需要创建一个公式:
=IF(Data!$C2="Fixed", Data!A2, "")
And copy that formula across all the cells.
然后在所有的单元格中复制这个公式。
But then I thought I'd get clever and have it not copy across blank lines, and entered a maze of unclear excel tutorials and vague error messages.
但后来我觉得自己应该聪明一点，不要让它在空行中复制，于是我进入了一个由不清楚的excel教程和模糊的错误消息组成的迷宫。
I've created this formula:
我创建了这个公式:
=INDEX(Data!A2:Data!A200, MATCH("Fixed", Data!$C$1:Data!$C$200, 0))
And entered it as an array formula using ctrl shift enter.
并使用ctrl shift enter以数组公式的形式输入。
However all I get is the contents of Data!A2 repeated over and over in every cell of my spreadsheet.
但是我得到的只是数据的内容!A2在我的电子表格的每个单元中反复地重复。
Does anyone need to know what I need to do to make this work?
有没有人需要知道我需要做什么才能让这个工作?
This is my attempt at a local prototype following the example in BruceWayne's answer, the results are visible:
在BruceWayne的回答中，我尝试了一个本地的原型，结果是可见的:
As you can see "Row 2" just appears repeatedly in the result column. Excel doesn't seem to have an easy way to see what version it is any more but this seems to be a pretty recent one, it's got the ribbon with the file menu and all menu headings are capitalized.
如您所见，“第2行”只是在结果列中重复出现。Excel似乎不太容易看出它是什么版本，但这似乎是一个最近的版本，它有带文件菜单和所有菜单标题大写的丝带。
1 个解决方案
#1
1
Here's a formula you can use (note: enter this as an array):
这里有一个您可以使用的公式(注意:输入这个数组):
=IFERROR(INDEX(A$1:A$200,SMALL(IF(C$1:C$200="Fixed",ROW(A$1:A$200)-ROW(A$1)+1),ROWS(A$1:A1))),"")
= IFERROR(指数(1美元:200美元,小(如果(C 1美元:200加元=“固定”,行(1美元:200美元)行(1美元)+ 1)、行(1美元:A1)))," ")
You may need to tweak the ranges, I think I got them to match yours, but again, probably need to tweak. You can then drag this down, and it'll fill in with the values from column A, skipping any blanks.
您可能需要调整范围，我想我让它们匹配您的范围，但同样，可能需要调整。然后你可以把它拖下来，它会填入A列的值，跳过任何空格。
Edit: Here's a screenshot to (hopefully) help show what I did: 
编辑:这里有一个屏幕截图(希望)帮助展示我所做的:
You can edit the ranges, naturally, to be over two sheets.
您可以自然地编辑范围超过两页。
To clarify, A$1:A$200 is the range of what you want to return (the index). C$1:C$200 is the range that holds "Fixed".
澄清一下，1美元:200美元是你想要返回的范围(指数)。C$1:C$200是“固定”范围。
#1
1
Here's a formula you can use (note: enter this as an array):
这里有一个您可以使用的公式(注意:输入这个数组):
=IFERROR(INDEX(A$1:A$200,SMALL(IF(C$1:C$200="Fixed",ROW(A$1:A$200)-ROW(A$1)+1),ROWS(A$1:A1))),"")
= IFERROR(指数(1美元:200美元,小(如果(C 1美元:200加元=“固定”,行(1美元:200美元)行(1美元)+ 1)、行(1美元:A1)))," ")
You may need to tweak the ranges, I think I got them to match yours, but again, probably need to tweak. You can then drag this down, and it'll fill in with the values from column A, skipping any blanks.
您可能需要调整范围，我想我让它们匹配您的范围，但同样，可能需要调整。然后你可以把它拖下来，它会填入A列的值，跳过任何空格。
Edit: Here's a screenshot to (hopefully) help show what I did:
编辑:这里有一个屏幕截图(希望)帮助展示我所做的:
You can edit the ranges, naturally, to be over two sheets.
您可以自然地编辑范围超过两页。
To clarify, A$1:A$200 is the range of what you want to return (the index). C$1:C$200 is the range that holds "Fixed".
澄清一下，1美元:200美元是你想要返回的范围(指数)。C$1:C$200是“固定”范围。