How to build bar graph of submissions by user

Patrick Dodd · 04/15/20

We are using Smartsheet to build a Change Management process for our IT department. The way it currently works is we have a form that users fill out containing all the details of their change request, which are recording to a spreadsheet. I'm building a metrics dashboard for this, and am trying to determine a way to build a graph showing the number of requests made by each requester.

On the form, one of the required fields is the name of the person submitting the request - we require them to type out the name because the person entering the data in the form isn't always the person making the request.

My current thought was to create a separate grid, link the Requester Name cells to this grid along with a tally of the requests they've submitted in a second column, and build a graph off of this.

The problem I'm running in to is new entries via the form aren't populating the Requester Name in the second spreadsheet. Does anyone know of a way to automate this so that every time a request is submitted, the Requester Name gets added to this second sheet, in turn updating the graph?

Any and all ideas are appreciated, thanks!

Rainier Hollands · 04/16/20

Hi @Patrick Dodd

If I were using a new grid for each of the requests, I would just have two columns (if I'm understanding you correctly), 1 - The name of the person and the number of requests made.

For the number of requests made I would use a countif formula, where I would say:

=Countif(reference another sheet and select the column where all the requests are entered, " Name of the person you looking up)

Hope this helps, if you have any other questions, feel free to ask!

Regards

Rainier

Patrick Dodd · 04/16/20

Hi @Rainier Hollands ,

Thanks for the reply! This solution sounds like the way I am approaching this, however, I'm having trouble determining a way to the name of the requester to the grid when they may a request, and only once to prevent duplicates.

I tried doing this using Linked Cells to automatically bring in all names of the requesters, but whenever a new entry is being made via the form, the name of the requester isn't being added to the new grid as I'd hope. From there the next challenge is removing duplicates as each requester is going to be submitting multiple issues.

Any ideas on how to solve this problem, or perhaps any other ideas as to an alternative approach? I just want this to be as automated as possible - the graph should dynamically create a new entry for each name in the Submitter Name column since it isn't always a set group of people submitting requests.

Thank you,

Patrick

Paul Newcome · 04/16/20

I recently helped someone else out with a similar solution where they wanted a dynamic list of each distinct entry. Let me dig through my notes, and I'll get back to you.

Paul Newcome · 04/16/20

I can't find the notes from the Community post, but I do have some others that may help.

HERE is a link to a sheet containing some formulas. Basically the way this works is you join all distinct values into a delimited string, then parse the string out down the List column. You would want to dragfill the formulas indicated in the sheet down far enough to provide room for the maximum number of distinct values you anticipate plus a buffer.

So if you expect to have 25 distinct names, then you would dragfill the formulas to cover maybe 35 rows to give yourself a little buffer. If you expect 50, dragfill for 65. That kind of thing.

How to build bar graph of submissions by user

Answers

Help Article Resources

Categories