Hi everyone,
I have a spreadsheet tracking monthly insurance payments. I want to create a list at the bottom of my spreadsheet that will populate with the individuals names that have not paid for each month. Each person spans across 4 rows. When someone loses coverage, I merge the 4 rows for the months they are termed. The problem I am having is when I try to use formulas to search for the unpaid people it returns the termed individuals since 3 of the 4 merged cells are considered blank. I am looking for a formula that will search only the first cell for every 4 rows (i.e. start at F2 and search every 4th cell after to see if it is blank, then use the index function to find what individual in row E has not paid). Below is as far as I could get:
=INDEX(F$2:F$181,4*(ROWS(F$192:F192)-1)+1)
I think I need to use the small function so that once they pay, it gives me the next individual who hasn't paid. Any help would be great. Thanks
Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting
I have a spreadsheet tracking monthly insurance payments. I want to create a list at the bottom of my spreadsheet that will populate with the individuals names that have not paid for each month. Each person spans across 4 rows. When someone loses coverage, I merge the 4 rows for the months they are termed. The problem I am having is when I try to use formulas to search for the unpaid people it returns the termed individuals since 3 of the 4 merged cells are considered blank. I am looking for a formula that will search only the first cell for every 4 rows (i.e. start at F2 and search every 4th cell after to see if it is blank, then use the index function to find what individual in row E has not paid). Below is as far as I could get:
=INDEX(F$2:F$181,4*(ROWS(F$192:F192)-1)+1)
I think I need to use the small function so that once they pay, it gives me the next individual who hasn't paid. Any help would be great. Thanks
Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting
