I want to list the names of members present. I have rows (2-43) with names and columns based on dates and if they are present I put a X in that cell in the column for the date. I want to list only those members with X's in a list.
I was able to create a list in column A with this formula =IF(B2 = "X", A2, ""). This listed the names from A2 down to A43. With blanks for those not present.
Now in cell D2, I have placed this formula. Which lists the names in the D2 going down. So, if 6 people were present, I get a list of 6 names in cells D2-D7. My issue is that the next cell without a corresponding name (D8) will give me a #NUM! error. If I remove the formula in column A the error resolves.
=IF(ROWS(D$2:D2)>COUNTA($A$2:$A$25),"",INDEX($A$2:$A$25,AGGREGATE(15,6,(ROW($A$2:$A$25)-ROW($A$2)+1)/($A$2:$A$25<>""),ROWS(D$2:D2))))
This formula is a bit too complex for me to be able to fix it, any suggestions.
D
I was able to create a list in column A with this formula =IF(B2 = "X", A2, ""). This listed the names from A2 down to A43. With blanks for those not present.
Now in cell D2, I have placed this formula. Which lists the names in the D2 going down. So, if 6 people were present, I get a list of 6 names in cells D2-D7. My issue is that the next cell without a corresponding name (D8) will give me a #NUM! error. If I remove the formula in column A the error resolves.
=IF(ROWS(D$2:D2)>COUNTA($A$2:$A$25),"",INDEX($A$2:$A$25,AGGREGATE(15,6,(ROW($A$2:$A$25)-ROW($A$2)+1)/($A$2:$A$25<>""),ROWS(D$2:D2))))
This formula is a bit too complex for me to be able to fix it, any suggestions.
D