IF Formula for task row containing mini tasks in columns in order from left to right

Marco de Oliveira

I have a document that is setup per client as the primary column. Each client row represents a task to complete audited financials each year for each client. Each column from left to right represents a sub-task during the audit process outlined below. I have setup each column to use conditional formatting to change the colour of the cell in that row to red if the sub-task has not been completed per the logical steps outlined below. I am busy setting up workflows to automatically alert the managers and request input by the responsible people for each sub-task if overdue. I would like to create reports and dashboards on all clients (rows) that have a sub-task overdue somewhere in each row so that we can have a live global overview for Director and management levels.

I was told that you can't create reports or health status based on conditional formatting in a row so I thus need to create a very complicated IF formula to change the health status of that row to red if any sub-task is overdue so I can report on health status OR I need to create a very complicated report builder based on every overdue sub-task in each row. Either way is fine as long as I can get the report and dashboard going on all the sub-tasks and each row main task.

I have outlined each sub-task of each row below and also provided a snapshot of what the row looks like. I would really appreciate it if someone could please guide me to the correct way of reporting this.

Sub-task 1: Submission Deadline - Bookkeeper must submit the audit pack to the auditor by the deadline date. Submission done checkbox must be ticked if submission deadline in past or task is overdue and red.

Sub-task 2: 1st queries received - auditor has 7 days from submission deadline to send 1st batch of queries if any. Bookkeeper would enter a date for when the 1st queries are received. If the 1st query deadline is in the past and no date is entered in the 1st queries received cell, then task is overdue and red.

Sub-task 3: 1st queries responded - bookkeeper has 2 days to respond to those queries once received. If 2 days have passed from the date entered in 1st queries received and the 1st queries checkbox is unticked, then task is overdue and red.

Sub-task 4: Final queries received - auditor has 12 days from submission deadline to submit final queries if any. If final queries received cell is empty and final queries deadline is in the past, then the task is overdue and red.

Sub-task 5: Final queries responded - bookkeeper has 2 days to respond to final queries. If 2 days have passed from date in final queries received cell and final queries responded checkbox is unticked, then task is overdue and red.

Sub-task 6: Draft AFS received - auditor has until the end of the same month of submission deadline to complete the audit and submit the draft financials to us. If the draft AFS deadline date is in the past and the drafts received cell is empty, then the task is overdue and red.

Sub-task 7: Team meeting held - team needs to meet within 2 days of receiving draft to discuss. If 2 days from drafts received date has passed and the teams meeting held checkbox is unticked, then the task is overdue and red.

Sub-task 8: Committee queries resolved - If committee queries = yes and committee queries resolved checkbox is unticked then task is overdue and red.

Sub-task 9: AFS Signed - the team has until the deadline noted in AFS Signing Deadline to get the financials signed by the client. If AFS Signing Deadline is in the past and the AFS signed checkbox is unticked, then task is overdue and red.

Sub-task 10: Signed AFS sent to auditor - if the AFS signed checkbox is ticked and the Signed AFS sent to auditor is unchecked then task is overdue and red.

Sub-task 11: Post audit adjustments done - if the AFS signed checkbox is ticked and the post audit adjustments done checkbox is unticked then the task is overdue and red.

Annotation 2020-05-23 141004.png

Ramzi K

Hi @Marco de Oliveira,

Is the main goal of the reports and dashboards to:

1. total counts of how many things are late? If it's the OR
2. show a list actual late tasks and sub-tasks

If it's 1, then try using the sheet summary and then basing a report or dashboard on that (good for metrics tracking)

If it's 2, have you considered using sub-tasks nested under tasks?

I know your information was very detailed, but if the main goal is to be able to base reports and filters on the "health" of tasks and sub-tasks then yes, you cannot base formulas on conditional formatting, but whatever you were going use as the rules for conditional formatting are the same rules you would use for health columns for each sub-task.

I hope that makes sense.

Please feel free to share more detail and I'll try to help you.

Cheers,

Ramzi

Ramzi K

Hi @Marco de Oliveira,

Is the main goal of the reports and dashboards to:

1. total counts of how many things are late? If it's the OR
2. show a list actual late tasks and sub-tasks

If it's 1, then try using the sheet summary and then basing a report or dashboard on that (good for metrics tracking)

If it's 2, have you considered using sub-tasks nested under tasks?

I know your information was very detailed, but if the main goal is to be able to base reports and filters on the "health" of tasks and sub-tasks then yes, you cannot base formulas on conditional formatting, but whatever you were going use as the rules for conditional formatting are the same rules you would use for health columns for each sub-task.

I hope that makes sense.

Please feel free to share more detail and I'll try to help you.

Cheers,

Ramzi

Marco de Oliveira

Hi @Ramzi. The main goal is point 2 in your feedback. As soon as any of the sub-tasks are late it delays the completion of main task and causes a spiral effect so I need to see on a global level the health of the main task and also the health statuses or maybe a graph of the sub-tasks progress etc so I can see as a company where our bottlenecks are. You have been helping me in other questions as I have been trying to build a nested IF formula for each row health status, but due to the number of sub-tasks, it is proving to be a massive formula.

That nested formula for health status would enable me to report on the health statuses of each main task but I would still need to figure out how to report on the sub-tasks unless I do a health status per sub-task in the same row and report on those too.

I am trying to find a way of ultimately getting all this data into a dashboard with a breakdown of all main tasks, sub-tasks, overall effectiveness of each "assigned to" staff member etc.

Thanks for all your help.