copying data from one sheet to another providing a specific condition is met

[robbarba]

Thank you and I appreciate all assistance!

Prior to updating to WIN10 and O365 I was able to have the following condition work

{=IFERROR(INDEX(Incident!P:P,SMALL(IF((Incident!$Q$2:$Q$301="Yes"),ROW(Incident!$P$2:$P$301)),ROWS(Incident!$p$11))),"")}

What this did was go to my Incident sheet and in column P look for the trigger "Yes" and if present return the value in a given cell. Everything is working with the exception of one cell that returning a "0" (ZERO) I have removed the IFERROR and I get the #NUM ! error.

{=IFERROR(INDEX(Incident!G:G,SMALL(IF((Incident!$Q$2:$Q$300="Yes"),ROW(Incident!$G$2:$G$300)),ROWS(Incident!$G$2:G3))),"")}
I have the same formula in multiple cells and they're reporting correctly. If you need data extracts let me know

 

[RasGhul]

Hi,

I like to lock down the ranges and avoid IFERROR on arrays where possible as it normally can show sheets down, maybe this construction may help you.

Just adjust the ranges as needed;

Book1
	P	Q
2	Value1	Yes
3	Value2	Yes
4	Value3
5	Value4	Yes
6	Value5	Yes
7	Value6	Yes
8	Value7
9	Value8
10	Value9	Yes
11	Value10	Yes
12	Value11	Yes
Incident

Book1
	B
2	Value1
3	Value2
4	Value4
5	Value5
6	Value6
7	Value9
8	Value10
9	Value11
Sheet2

Cell Formulas
Range	Formula
B2	{=IF(ROWS(Sheet2!$B$2:B2)>COUNTIF(Incident!$Q$2:$Q$1000,"Yes"),"",INDEX(Incident!$P$2:$P$1000,SMALL(IF(Incident!$Q$2:$Q$1000="Yes",ROW(Incident!$P$2:$P$1000)-ROW(Incident!$P$2)+1),ROWS(Sheet2!$B$2:B2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

I have removed the IFERROR and I get the [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NUM]#NUM [/URL] ! error.

{=IFERROR(INDEX(Incident!G:G,SMALL(IF((Incident!$Q$2:$Q$300="Yes"),ROW(Incident!$G$2:$G$300)),ROWS(Incident!$G$2:G3))),"")}
I have the same formula in multiple cells and they're reporting correctly. If you need data extracts let me know

1. That would likely indicate a #NUM ! error already in columns P or Q of 'Incident', most likely column P if that 2nd formula is working correctly. If so, you need to revisit that data and try to eliminate any errors.

2. BTW, what sheet name are these formulas on?