I want tabs in my Excel sheet to reference fields in a different page if criteria is met; if the criteria is not met, I want to skip that row, and move to the next row where the criteria is met.
My database houses individuals who have been diagnosed with cancer (breast and prostate). I want the breast tracking page to only populate individuals who have been diagnosed with breast cancer (referencing the “Participant Information” sheet; column D). Currently I used an IF
statement to populate this information, but my sheet populates empty rows because those rows are prostate cancer (meaning they do not meet the IF
statement criteria).
I have found this formula:
{=IFERROR(INDEX('Participant Information'!A:A,SMALL(IF('Participant Information'!$D$2:$D$300="Breast",ROW('Participant Information'!$D$2:$D$300)),ROWS(1:1))),"")}
But I cannot understand how it works. It also it not working for my purpose.
TL;DR: I need to reference cells on a separate tab, without having blank rows when the criteria isn't met. I think of the "Next Record" function that is used in Mail Merge.
FILTER()
function it would be easy to understand. And if you are not using theMS365
per your tag, then useINDEX()
&AGGREGATE()
function, you can exclude blanks as well.=IFERROR(INDEX('Participant Information'!A$2:A$300,SMALL(IF('Participant Information'!D$2:D$300="Breast",ROW('Participant Information'!D$2:D$300)-ROW(D$2)+1)),ROWS(1:1))),"")
AGGREGATE()
you wont need to hit theCTRL
+SHIFT
+ENTER
while exiting the edit mode which you are presently doing using your current formula, that said:=IFERROR(INDEX('Participant Information'!A$2:A$300,AGGREGATE(15,6,(ROW('Participant Information'!D$2:D$300)-ROW(D$2)+1)/('Participant Information'!D$2:D$300="Breast"),ROWS(1:1))),"")
-->15
meansSMALL()
while6
means to ignore errors values. Lemme know if that works.