Orderable Charts

Floretti

Good morning, I've done several searches here but now I'm going in circles.

I have a sheet with records of usage of certain scripts we have. Each row has the user name, the script they used and the time they saved by using each script.

My goal is to create a dashboard with two bar charts:

• user name vs total time savings (ordered by highest time savings)
• most used scripts (ordered highest count)

The charts are ready but the bars can't be reordered which makes them pointless. Also, the reports they've been created from can't be ordered based on the sums, which means this approach didn't work. I then tried using a blank sheet with formulas showing the total savings per user and create the chart on top of that but now my chart won't update when a new user shows up in the sheet.

Can anyone see a way I can achieve that? Thank you :-)

AravindGP

I understand. Unfortunately, there is no other option that I see. One thing to make it easier for you to add the name is to have a helper column in the source sheet with a formula to identify duplicates. If there is a record with no duplicate (i.e., a new name added), you can get notified through a workflow and use that to add the name to your metric sheet.

AravindGP

You can use the below formula in a helper checkbox column. Using this formula, the helper column you create will be checked when there is no duplicate entry. You can then use the helper column being checked as the trigger to notify yourself to create the name in the metrics sheet.

=IF(COUNTIF([User Name]:[User Name], [User Name]@row)>1, 0, 1)

Genevieve P.

Hi @Floretti

Try adding an @cell reference in your formula, this can sometimes help with COUNTIFS looking at a number/text value:

=IF(COUNTIF([User Name]:[User Name], @cell = [User Name]@row)>1, 0, 1)

Let us know if that helped!

Cheers,

Genevieve

AravindGP

Hi There,

I assume the sheet you've that is collecting the data is similar to the screenshot you have added. This means that you can't really get the number of times a script is used without the help of sheet summary or a separate metrics sheet to count the number of times a script has been used. In the metrics sheet, you can add in the unique users and do a sumif formula to get the time saved across all scripts. When you have a new user added to the source sheet, all you have to do is add the name of the new user to your metrics sheet and it will auto do the formula to get the time saved sum for that user. You can have the column of time saved in your metric sheet sorted to be highest to lowest and then modify your chart to have the values listed in the desired order.

Floretti

Thanks for the reply, Aravind. That definitely looks like an option I can use. Do you see any way to avoid the manual intervention when another user is added?

We currently have a large number of users and high turnover, which means I'll have new names turn up very often and I'm expecting it to be hard to spot the missing ones amongst all the names.

AravindGP

I understand. Unfortunately, there is no other option that I see. One thing to make it easier for you to add the name is to have a helper column in the source sheet with a formula to identify duplicates. If there is a record with no duplicate (i.e., a new name added), you can get notified through a workflow and use that to add the name to your metric sheet.

Floretti

That could work, thanks heaps Aravind. I'll try and implement that.

Floretti

@AravindGP I realise it is off topic but any tips on how to achieve the formula to identify duplicates in the helper sheet? The rest is sorted as you suggested but I have no idea how to implement that part. Thank you.

AravindGP

You can use the below formula in a helper checkbox column. Using this formula, the helper column you create will be checked when there is no duplicate entry. You can then use the helper column being checked as the trigger to notify yourself to create the name in the metrics sheet.

=IF(COUNTIF([User Name]:[User Name], [User Name]@row)>1, 0, 1)

Floretti

Thanks so much but something very odd happened. It's considering the user 1104825 as a new user. Note that the user name is added via API and the user cell is fed the string "1104825", which ends up as '1104825 in Smartsheet. I wonder if that's what is causing the issue.

Genevieve P.

Hi @Floretti

Try adding an @cell reference in your formula, this can sometimes help with COUNTIFS looking at a number/text value:

=IF(COUNTIF([User Name]:[User Name], @cell = [User Name]@row)>1, 0, 1)

Let us know if that helped!

Cheers,

Genevieve

Floretti

@Genevieve P. Yep, that did the job, thank yoooou! :-)

@AravindGP Thanks again, I very much appreciate the help.