Hello everyone and thank you for checking this thread.
My problem is kind of complicated but I'll give you a general idea of what I'm hoping to achieve.
I am creating an audit sheet. I have an "Expectation", "Measurement Criteria", "Weight", "Number of Deficencies", "Score", "Positive Comments" and "Improvements Needed" columns. I am attempting to retrieve a typed in comment from the "Positive Comments" column and place it on a summary sheet at the end of my audit sheet. I have gotten it to retrieve the typed in comments, however, if I have an empty cell it copies that to the summary section as well which looks rather unprofessional.
My formula is:
=IFERROR(IF(IF(ROWS(D$9:D$77)>=COUNTIF(D$9:D$77,D$9:D$77=0),INDEX(F$9:F$64,SMALL(IF(D$9:D$77=0,ROW(F$9:F$77)-MIN(ROW(C$9:C$64))+1),ROWS($9:9))),"")=" ","",IF(ROWS(D$9:D$77)>=COUNTIF(D$9:D$77,D$9:D$77=0),INDEX(F$9:F$64,SMALL(IF(D$9:D$77=0,ROW(F$9:F$77)-MIN(ROW(C$9:C$64))+1),ROWS($9:9))),"")),"")
Now, while I don't think that this is the easiest thing to piece backwards, I am wondering if anyone has any ideas as to how to make this formula (or a similar formula at that) ignore the blank cells. I have gotten it to work on the "Improvements Needed" column (which I did first), but I can't quite figure out what I need to change to get it to return what I want it to.
Thanks for your help in advance!
My problem is kind of complicated but I'll give you a general idea of what I'm hoping to achieve.
I am creating an audit sheet. I have an "Expectation", "Measurement Criteria", "Weight", "Number of Deficencies", "Score", "Positive Comments" and "Improvements Needed" columns. I am attempting to retrieve a typed in comment from the "Positive Comments" column and place it on a summary sheet at the end of my audit sheet. I have gotten it to retrieve the typed in comments, however, if I have an empty cell it copies that to the summary section as well which looks rather unprofessional.
My formula is:
=IFERROR(IF(IF(ROWS(D$9:D$77)>=COUNTIF(D$9:D$77,D$9:D$77=0),INDEX(F$9:F$64,SMALL(IF(D$9:D$77=0,ROW(F$9:F$77)-MIN(ROW(C$9:C$64))+1),ROWS($9:9))),"")=" ","",IF(ROWS(D$9:D$77)>=COUNTIF(D$9:D$77,D$9:D$77=0),INDEX(F$9:F$64,SMALL(IF(D$9:D$77=0,ROW(F$9:F$77)-MIN(ROW(C$9:C$64))+1),ROWS($9:9))),"")),"")
Now, while I don't think that this is the easiest thing to piece backwards, I am wondering if anyone has any ideas as to how to make this formula (or a similar formula at that) ignore the blank cells. I have gotten it to work on the "Improvements Needed" column (which I did first), but I can't quite figure out what I need to change to get it to return what I want it to.
Thanks for your help in advance!
