I have a spreadsheet with two sheets. On the first sheet, there is a list of policies and the date they are up for review. Using conditional formatting, I have managed to highlight the policies that are due.
On the second sheet, I am trying to list all of those policies that are due. Below is my formula:
=IFERROR(INDEX(Policies!A$3:A$130, SMALL(IF(Policies!D$1=Policies!E$3:E$130, ROW(Policies!A$3:A$130)-ROW(Policies!$A$4)+1), ROW(1:1))),"")
This formula works to a degree. The first policy it retrieves is correct, but every policy after that is off by one row. I put $ before every reference to the cells on the Policies sheet because when I dragged the formula down it incremented all by one which made it invalid. I suspect my problem is something to do with the ROW part of the formula, but I've been trying for days and haven't managed to figure it out. Is anyone able to point me in the right direction?
Bookmarks