I have a list of construction projects called “work orders” and another list of construction projects called “MOCs”. I am tasked with showing the remaining revenue for each project based upon work done.

Each project has a revenue associated with it based on materials used. For quick example, if I place one flower pot or brooks box I get $1 for each placement.

Now, I’m using excel as a dashboard/project list such that all the math for the revenue is done on a few different sheets in the workbook while there is 1 sheet of the workbook used to display all the total revenue, project name/due date.

I am using “hide row” as a way to represent a “finished project”. Once I hide a row, I’d like the project and the value to be disregarded from the dashboard.

The problem I’m facing is that, even though I’m hiding the rows upon project completion, some values that are hidden will still populate on the dashboard. It seems that hiding a row is not the best way to represent “project completion”.

Does anyone know if there is a better way to represent a “completed” row that needs to be hidden or otherwise disregarded? Thank you in advance!

I have been able to partially solve this by using SUBTOTAL() instead of SUM() to get the total revenue of all remaining projects.

Ideally, I would like to use the Fill Down to update the list of projects and revenues but currently using fill down just lists all the projects (including hidden ones).

Essentially, I’m looking to see how I could use Fill Down on the front-sheet dashboard while referencing a back-sheet that does the math but on that back-sheet I may have hidden rows that I do not want to show up on the front-sheet. I have not been able to find a better way to represent “complete projects”.