One Approval for Multiple Lines/Whole Sheet

09/01/21
Accepted

Hi everyone,

I have stuck with this problem for a while and seen many questions about how to consolidate the approval requests to be sent in one email. It seems like the only solution so far is to create a report with a approval column that has checkboxes for approve, deny, etc.

My concern with this way is that, in case the report have 200 lines, the approver will have to go through every line and check 200 boxes which is not so efficient.

Is there any other workaround that allows the approver to check one box only and it will automatically apply for the rest? Think about the paper report that only needs one signature from the manager for the approval.

My thought for now is to create an approval sheet in which one line represents its connected report/sheet and the approver will check the box there after he reviewed the report/sheet. Then use formula to indicate the status in the status column in the original sheet.

Thanks in advance for any help/comment!

Best Answer

  • Genevieve P.Genevieve P. admin
    Accepted Answer

    Hi @Lila De Vera

    If you want one approval checkbox to represent a number of rows, then yes! A formula should be able to work for your scenario.

    You could either have one "Approve" sheet where only the main approvals are stored, then have cross-sheet formulas to populate the status in the other sheet.


    For another option, what about using a hierarchy in your current sheet? You could have the Parent row be the full approval, and have all the child rows automatically become approved if the top Parent row has a checkmark:

    You could use an Update Request as a sort of approval workflow, and have a helper column identify what rows are Parent and what rows are Children so that your Update only goes out via email if it's a Parent row. Then if the checkbox is checked, all the Children change to "Approved" (using a formula).

    If you want the Approver to be able to see all the child rows associated, you could bring the Parent name into those Child rows (see my "Parent Name" column) and then either filter the sheet or create a Report with all 200 rows.


    Parent Column Formula:

    =IF(COUNT(ANCESTORS([email protected])) = 0, "Parent", "Child")

    Parent Name Column Formula:

    =IF(PARENT([email protected]) = "", [email protected], PARENT([email protected]))

    Status Column Formula:

    =IF(AND(COUNT(ANCESTORS([Approve?]@row)) = 0, [Approve?]@row = 1), "Approved", IF(PARENT([Approve?]@row) = 1, "Approved", ""))


    Cheers,

    Genevieve

Answers

  • Genevieve P.Genevieve P. admin
    Accepted Answer

    Hi @Lila De Vera

    If you want one approval checkbox to represent a number of rows, then yes! A formula should be able to work for your scenario.

    You could either have one "Approve" sheet where only the main approvals are stored, then have cross-sheet formulas to populate the status in the other sheet.


    For another option, what about using a hierarchy in your current sheet? You could have the Parent row be the full approval, and have all the child rows automatically become approved if the top Parent row has a checkmark:

    You could use an Update Request as a sort of approval workflow, and have a helper column identify what rows are Parent and what rows are Children so that your Update only goes out via email if it's a Parent row. Then if the checkbox is checked, all the Children change to "Approved" (using a formula).

    If you want the Approver to be able to see all the child rows associated, you could bring the Parent name into those Child rows (see my "Parent Name" column) and then either filter the sheet or create a Report with all 200 rows.


    Parent Column Formula:

    =IF(COUNT(ANCESTORS([email protected])) = 0, "Parent", "Child")

    Parent Name Column Formula:

    =IF(PARENT([email protected]) = "", [email protected], PARENT([email protected]))

    Status Column Formula:

    =IF(AND(COUNT(ANCESTORS([Approve?]@row)) = 0, [Approve?]@row = 1), "Approved", IF(PARENT([Approve?]@row) = 1, "Approved", ""))


    Cheers,

    Genevieve

  • Lila De VeraLila De Vera ✭✭✭✭✭

    Great idea! I will play around with it. Thank you

Sign In or Register to comment.