Hierarchy issues

KC Jones

Hi everyone,

I have been pursuing the forum but I can't seem to find the answer. For some background, my company had a team that set up our Workspace and sheets. One of my coworkers was also the admin but recently left for another job. I have been tasked with taking over admin/owner duties now. I understand the basics and I'm learning more about things like automation, but since I didn't set up the sheets, I'm sometimes not sure how to handle issues.

Today while fixing some widgets for a project dashboard, I noticed one that shows rows from a sheet was completely blank (just column headers and one blank row). I opened the widget to edit and saw it's pulling from a sheet I haven't used yet. I opened that sheet and it has nothing in it because of the applied filters.

Since it is linked in from the project sheet I checked it and the column for hierarchy is almost entirely zeros:

I am not sure how much of out info is confidential so my apologies but I felt just showing this would cover me. To explain, our project uses rollups above the purple line and at the end of the sheet, here we manually track percentage of milestones. This works well for other projects that have a start and end date but this is a continuous project so a lot of the categories can't really be tracked the way we do with other projects.

Below the purple line is the list of units/sites for the project, and PO# , status, etc. (red is cancelled). Initially I thought it was just because they are sites that haven't been started and/or delivered yet, but I checked rows that are, and they are also all zero. The formula for these cells is =COUNT(ANCESTORS()), but I don't really think that is what needs to be counted? What I really need is a way to automate changing hierarchy number based on a status column. At least if I'm going to pull sites into this list that is linked to the dashboard. I could change it to not filter for those but that would include data we don't need too. I need to be able to see only the ones that are in process and completed sites that aren't cancelled or removed (we use both terms to indicate when site was removed/cancelled).

I already have decent excel skills but this is still a learning curve for me. Can anyone help? If so try to use layman's terms. I'm not great with jargon, especially if it isn't applicable in my job.

Paul Newcome

Double check the rest of the filters as well.

Create a filter on the source sheet that basically mimics the report filter with Summary being unchecked and BU# not blank. What comes up there?

KC Jones

I tried to filter my source sheet but it just pulled everything in. To be honest I have no idea what we use hierarchy for other than conditional formatting in the main project sheets. However, you gave me an idea. Instead of trying to get the hierarchy to change, I changed the filter to status and selected only the values I need and it worked!

I know it wasn't exactly what you said but you got me to the answer! I'm probably going to leave the template alone for now just in case it messes things up.

Paul Newcome

Glad you were able to get something working. 👍️