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

robbarba

Board Regular
Joined
Apr 17, 2016
Messages
79
Office Version
  1. 365
Platform
  1. Windows
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$1:p1))),"")}

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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
PQ
2Value1Yes
3Value2Yes
4Value3
5Value4Yes
6Value5Yes
7Value6Yes
8Value7
9Value8
10Value9Yes
11Value10Yes
12Value11Yes
Incident



Book1
B
2Value1
3Value2
4Value4
5Value5
6Value6
7Value9
8Value10
9Value11
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0
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?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top