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.
