Moving rows/updating cells

Paula D

I manage a portfolio of projects which I have to report on. I'm a Pro user, so I don't have the functionality that could probably make this task very easy. My project managers are required to use a company solution (not smart sheet to create project plans and updates). I've created a smart sheet that lists all of the projects along with various data (phase, go live date, implemented date, comments for high level updates, etc...). The issue I'm running into is my boss wants data reported and it involves a lot of filtering (think pivot table that isn't included in the Pro version). My dashboard doesn't take into account the filtering logic when reporting statuses. I got the dashboard to work if I broke out the individual filtering onto separate sheets. So as an example, any projects on hold, I used automated logic from the main portfolio and copied the row out to an "On Hold" spreadsheet. I then take the count from the on hold spreadsheet and display on the dashboard. My issue and what I need help with is when the on hold status changes to anything else, the copied row to the On Hold spreadsheet isn't dropping off. It remains on the On Hold spreadsheet, but now it may say Planning. In the main spreadsheet, where the project was updated, everything looks fine. How do I get the copied row to essentially delete when the status changes?

status.jpg

Or is there another way to do this? Cell linking updates the sheet, but again the changed status is now in the "wrong" spreadsheet.

Any suggestions would be greatly appreciated.

dashboard.jpg

Genevieve P.

Hi @Paula D

Copying rows will copy the information statically, as you've found. The rows won't be synced back to the original sheet unless you've applied Cell Linking.

What I would suggest is create a Report instead of using separate Sheets. Reports create a view from the underlying sheet, but it uses the exact same rows which means that as the sheet is updated the data in the Report will update, too.

You can create a Report on a Pro plan, but you can only have one sheet as the source. This sounds like it will work for your purposes!

After you've created the Report, I would use the Grouping feature to group by the Status. Then you can apply a Summary to that column to COUNT how many rows are in each Group.

Once you have both Grouping and Summary applied, you can use this Report as the Source for your Chart Widget. This will then show the numbers for all of your status options in one chart! These numbers will update automatically as your sheet updates.

If you prefer the separate bar charts, then you could create multiple Reports and Filter before you Group/Summarize.

Here's a free webinar on Grouping and Summary in Reports: Redesigned Reports with Grouping and Summary Functions

Let me know if this makes sense and will work for you!

Cheers,

Genevieve

Genevieve P.

Hi @Paula D

Copying rows will copy the information statically, as you've found. The rows won't be synced back to the original sheet unless you've applied Cell Linking.

What I would suggest is create a Report instead of using separate Sheets. Reports create a view from the underlying sheet, but it uses the exact same rows which means that as the sheet is updated the data in the Report will update, too.

You can create a Report on a Pro plan, but you can only have one sheet as the source. This sounds like it will work for your purposes!

After you've created the Report, I would use the Grouping feature to group by the Status. Then you can apply a Summary to that column to COUNT how many rows are in each Group.

Once you have both Grouping and Summary applied, you can use this Report as the Source for your Chart Widget. This will then show the numbers for all of your status options in one chart! These numbers will update automatically as your sheet updates.

If you prefer the separate bar charts, then you could create multiple Reports and Filter before you Group/Summarize.

Here's a free webinar on Grouping and Summary in Reports: Redesigned Reports with Grouping and Summary Functions

Let me know if this makes sense and will work for you!

Cheers,

Genevieve

Paula D

Thank you for your comment. I had created reports which gave me the data I had wanted, but I couldn't use those reports as bar/chart graphs on the dashboard. That's why I went to the path of copying/moving cells. What you described would solve my issue if I were a business or enterprise subscriber. Unfortunately, I pay for this out of my own pocket and being that's there's a 3 person min license, this just isn't financially feasible. I appreciate the offer for help

Genevieve P.

Hi @Paula D

My apologies! You're correct, Pro plan doesn't have the ability to Group and Summarize from within a Report.

Not to worry, we can create the exact same calculations by creating a new sheet (I would call it something like 'Metric Sheet') then use cross-sheet formulas looking into your source sheet to create numbers.

A COUNTIFS formula will allow you to return a number based on the number of rows with a certain status. In your Metric sheet, have one column list out your Status values. Then in a column next to it, use a formula like this:

=COUNTIFS({Status Column Source Sheet}, [Status Column]@row)

This formula will look into the column you specify in the {cross sheet reference} and count how many rows having a matching value to the cell in your "Status Column".

Then you can use this chart in the new sheet as the source for your widget. Let me know if it would be easier to see screen captures of this to help explain.

Cheers,

Genevieve