The code in cells L3-L7 works on the "Original" sheet of the attached test spreadsheet. When individual sections of the sheet are moved to their own sheets (1-5), and the corresponding code is placed in cells A2-A6 of the "Approval Outstanding" sheet, I encounter two unwanted phenomena:
Unlike in 6StringJazzer's solution in the "Original" sheet, when the code in the "Approval Outstanding" column A finds no missing Approved date in column H of the sheet it looks the data up in, the cell in column A on "Approval Outstanding" sheet
- displays "0" or remains empty. Unlike on the "Original" sheet, the cell doesn't get filled with the results of the first next line calculation that reflects the fact an Approved date is missing on the sheet it refers to. So the result is that instead of having all the findings of outstanding approvals listed one after another without a gap, they are separated by rows corresponding to sheets with no outstanding approvals. I presume it is because each row in column A of the "Approval Outstanding" refers to different sheet data. Is there a way to overcome this shortcoming?
- The first instance of a sheet (1-5) that has no missing Approved date, produces "0" in the corresponding cell in column A on "Approval Outstanding" sheet. Each additional instance produces an empty cell. Is there a way to make replace the "0" with empty cell?
Bookmarks