CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsformulas
Aldaen
Aldaen

Reputation: 11

How to generate a task list showing time remaining

I have a task list in Google Sheets. It has several columns, but for the sake of my problem, I only care about two:

At the bottom of F is the total number of hours for the project overall. What I want to add is an hours remaining cell, which would take the total number of hours in F and subtract hours based on a "Complete" status in the same row of D.

One way I can think to do this is with conditional formatting, changing rows to green when the status is changed to Complete. That I can do.

I can then Sum F by Color (green), and subtract that number from the project total.

That works. The problem is that there isn't a way to get the whole sheet to do that. I have to put a conditional formula in each row and modify it for each row.

In other words, for row 3, I have to use: =$D$3="Complete" Then for row 4, I have to use: =$D$4="Complete"

This would obviously be a long and tedious process for a long task list.

Any better ideas how to solve this problem? Or is a script necessary?

Upvotes: 1

Views: 98

Answers (1)

S van Balen
S van Balen

Reputation: 288

I would make a 3rd row , lets call it G, which would represent something like "hours in, provided not completed" and sum that column. You can just put in a formula in that column that goes:

IF(D1 <> "Complete", F1, 0)

Just drag that value from row 1 to end, and SUM it at the bottom.

Upvotes: 1

Related Questions