38

Is this possible?  I went to try and create a metrics sheet from the report but it doesn't show up.  Building the report was supposed to group all of my similar items together, why can i not then build a metric sheet to create my Dash based upon that report?

Comments

Andree_Stara

Hi Mike,

Not sure I follow!

To add to the Metric Widget you'll have to use a sheet. 

What do you want to do?

Have a fantastic week!

Best,

Andrée Starå - Workflow Consultant @ Get Done Consulting

I agree with Andree.  To use the Metric Widget, the source comes from a sheet, however, if you do have a Report created, you can use the Report Widget and show the entire Report in the Dashboard.

Hope that helps.

In reply to by [email protected]

I am not trying to use the metrics widget, but to build a metrics sheet which i can then use to build a graph or chart.  My original sheet that I am pulling from houses many types of information from various 'customers' the report then makes it so each 'customer' can only view their own information.  I would then in turn like to build dashboards for each individual 'customer' as well.  If i cannot use the report to pull data from, in what way can i build a sheet to correctly pull the information i want for only a specific customer?  If column x = customer z then attain completion status of column y for same row?

Andree_Stara

In reply to by MikeCorvin

Ok.

Now I follow!

My recommendation would be to create a master metric sheet where you collect everything witch cell-links or/and cross-sheet formulas from the other sheets. You can then use that information in reports and dashboards.

Would that work?

Best,

Andrée

When using cross sheet references, you cannot select a report to reference. It has to be a sheet. Try referencing the sheet that the report is pulling from and using the appropriate criteria needed.

I am not trying to use the metrics widget, but to build a metrics sheet which i can then use to build a graph or chart.  My original sheet that I am pulling from houses many types of information from various 'customers' the report then makes it so each 'customer' can only view their own information.  I would then in turn like to build dashboards for each individual 'customer' as well.  If i cannot use the report to pull data from, in what way can i build a sheet to correctly pull the information i want for only a specific customer?  If column x = customer z then attain completion status of column y for same row?

In reply to by MikeCorvin

If you are building out separate dashboards from each customer, you could compile the info you want displayed for each customer all in one sheet. Are you able to provide some screenshots (use dummy data if there is any sensitive/confidential information that can't be displayed)?

 

What I am thinking is using a table with COUNTIFS, SUMIFS, and INDEX/MATCH formulas along with cross sheet references to pull your pertinent data, but a lot of it is going to depend on your current setup and how/what exactly you are trying to display the data in the end.

Here is a quick shareable version of what I have created with some dummy data in it.  The Shareable Test is the main sheet while the report is labeled as such.  There a re a few metrics i would be interested in for creating charts/graphs, the variations in the dates, the amount of times any given material is reported, and down the road possibly how many times we have issues of foreign materials under the same PO#s.

And how are you wanting the data displayed? Chart/graphs? Tables?

Charts and Graphs I'm thinking, however if I can pull the information into a metrics sheet then I can build anything from it.

Ok. Depending on the number of variables you are reporting on, it could require a handful of sheets for organizational purposes.

 

Basically what you would do would be to build out your table in the metrics sheet(s). Then in your formulas, you would use cross sheet references to mimic the criteria you were wanting to pull for the report.

 

I am having trouble figuring out the details from your above posted screenshots. Are you able to post other ones with the sheet names visible? It makes it easier to tell which one is which. Also a mockup of how you'd like the metrics sheet to work along with more detail on exactly where the information is coming from would help.

In reply to by Paul Newcome

Paul,

I have retaken shots and will add them to this reply.  The one that reads as Test with 'Suppliers' listed as 1, 2, & 3, would be the main sheet (from which i would want to pull the metrics from), whereas the Report shows only 'Supplier 1'.  Creating a metrics sheet would be wonderful, as it would allow me any number of possibilities for future building in a Dashboard or etc,  building sheets as such hasn't normally been a huge issue, but by adding the variable of specifying a supplier to pull information on I am unsure how to go about creating that formula.  Obviously I cannot just draw information from the report, which would be easier as it is already sorted, would be nice if it offered If Then loops but maybe we'll get there one day.

Mike

Ok. It's making more sense now.

 

There are a ton of different ways to build out the sheet(s) depending on what you want to display and how. To specify a Supplier in a formula, you would use simple cross sheet referencing.

 

If you wanted to count how many times the word "Organic" showed up in the [Foreign Material] column, you would use

 

=COUNTIFS({Foreign Material Column Range}, FIND("organic", LOWER(@cell)) > 0)

 

Using the proper cross sheet referencing steps you would select the [Foreign Material] column from your sheet, and this will give you the number of times that word showed up in that range.

To add in the condition of the supplier:

 

=COUNTIFS({Foreign Material Column Range}, FIND("organic", LOWER(@cell)) > 0, {Supplier Column Range}, @cell = "Supplier Name")

 

Use the same cross sheet referencing steps to select the Supplier column and the specify the supplier. 

 

Of course you can build out a table and use cell references in place of the criteria, so on and so forth, but that's the general idea. Just keep adding range/criteria sets and you can pull as much and as detailed information as you want.

 

Depending on what you are pulling, you could use one metrics sheet for each supplier and have the foreign material types tracked or one for each foreign material type and get a total for each supplier on the same sheet, or you could have one sheet with parent rows as the supplier and a breakdown of material type as children or the other way around. The actual setup is entirely dependent upon what works for you based on the data you want to pull and how you want to display it.

In reply to by Paul Newcome

Thanks Paul,

I'll try this out over the course of the weekend and see what I can do with it.  If i hit any snags i know who to ask! though I'll try not to ask anything until the workweek starts back up again haha.

Mike

No worries. I don't get on here during the weekends anyway. Hahaha. Best of luck to you!

In reply to by Paul Newcome

Paul,

It works wonderfully! I only have two questions,

1) Would i be able to add the LOWER to the supplier name as well to account for other user entry?

2) What does the (@cell) part do exactly? I'm curious about its function.

Mike

Happy to help! yes

.

In answer to your questions...

1: Most definitely. This is a common practice for me. I use LOWER any time text is present just to ensure everything is accounted for. There are two different ways that I do this depending on the circumstances. I will either go ahead and create a column on the main sheet that will change it to lower, or I will use LOWER within the metrics sheet. Your question of using it in the metrics sheet would have the following solution:

 

=COUNTIFS({Foreign Material Column Range}, FIND("organic", LOWER(@cell)) > 0, {Supplier Column Range}, LOWER(@cell) = "supplier name")

 

Additionally, I will try to find a specific portion of the text that will always be the same each time that supplier is entered but is unique to that supplier and use that as a reference. For example:

 

If one supplier is technically called "The Mattress Shack" and it is the only one with the word "Mattress" in the name, I will search for that word only. That way if someone leaves off the "The" or changes it to "The Mattress Place", it will still be included in the correct data because I am searching on the word "Mattress" instead of the specific supplier's name. Of course I build the LOWER function into this as well.

 

Long story short... Keep thinking outside of the box, and there are a ton of ways to manipulate data to account for user error and have it pulled the way it needs to be.

.

2: The @cell reference basically just tells a formula to look at each individual cell within a range instead of the entire range as a whole.

In reply to by Paul Newcome

Paul,

Ahhhh I see what you mean awesome! With LOWER and @cell it makes it highly comprehensive.

Is there anything you know that would be able to register that one cell contains the same word more than once and count them both?  IE if a cell said Organic / Organic it would register that as 2 entries?

The "easiest" way would be to parse out the cells on the main sheet. Parsing within Smartsheet can be a pain, but if you have no more than 2 entries, it can be pretty straight forward.

 

What is the maximum number of entries you could have per cell?

Ok. So you would need three helper columns for starters.

 

Will it always be a / as a delimiter?

In reply to by MikeCorvin

Helper columns are just extra columns used to break automations down into smaller parts. It makes the more complex stuff easier to work with and allows you to troubleshoot much more effectively. The helper columns can be hidden after everything is up and running to keep your sheet looking clean.

 

I have a pretty busy schedule today, but I'll work on getting you some specifics for the setup when I can.

In reply to by Paul Newcome

I appreciate that Paul!

No rush, you have been amazingly helpful and I honestly wouldn't be able to put half of this project together without your help, the least i can give you in return is plenty of time haha.

Mike

Out of curiosity... How many different types can be entered into the Foreign Material column?

 

Organic

Plastic

Metal

Foam

Glove

 

are what you have listed in your screenshot...

In reply to by Paul Newcome

One more guaranteed one would be wood, after that it honestly will depend, there is no telling what else may show up one day, but for now those are the main ones as I tried to make the categories vague in order to group them more effectively, and make the next column the one that gets specific.

Ok. I have the parsing solution. I was curious about the listings in case that too could be streamlined. I will attach a screenshot of everything in action, but here is a basic breakdown of everything as well.

 

I am using 4 Helper columns.

Note: I kept the column names condensed because I have a habit of missing a square bracket somewhere. You can use whatever you want for column names.

.

MaterialHelper:

=LOWER(SUBSTITUTE([email protected], " ", ""))

This first one removes any spaces and converts everything to lower case. The reason for removing spaces, is that the parsing formulas use specific numbers (+1 or -1).

Removing the spaces turns all of the delimiters into "/". That way it won't matter if there is a space before, a space after, or spaces on both sides. As long as the slash is there, you're good to go. This helps with consistency of data which always makes things easier in the long run.

I used the LOWER function for the same reason. Consistency in data.

.

HelperA:

IF(FIND("/", [email protected]) = 0, [email protected], LEFT([email protected], FIND("/", [email protected]) - 1))

This uses the LEFT function to pull that first selection. I had to include the IF portion to say that if there was no "/" (meaning there is only one type), then just duplicate what is in the MaterialHelper column.

.

HelperB:

IF(FIND("/", [email protected]) = 0, "", IF(FIND("/", [email protected], FIND("/", [email protected]) + 1) > 0, MID([email protected], FIND("/", [email protected]) + 1, FIND("/", [email protected], FIND("/", [email protected]) + 1) - FIND("/", [email protected]) - 1), IF(FIND("/", [email protected]) > 0, RIGHT([email protected], LEN([email protected]) - FIND("/", [email protected])))))

This was the trickier one. Fortunately there will only be 3 options, so it's the only one.

Basically it says that if there isn't a "/" (one selection), then leave the cell blank.

If there are 2 of them (which means 3 selections), then we used a MID function to pull what was between them.

If there wasn't 0 and there weren't 2, then the only option left was 1 "/" which denotes two selections. Since there is only one "/", we were able to use the RIGHT function to pull whatever comes after the "/".

.

HelperC:

IF(FIND("/", [email protected], FIND("/", [email protected]) + 1) > 0, RIGHT([email protected], LEN([email protected]) - FIND("/", [email protected], FIND("/", [email protected]) + 1)))

Since we know that 3 is the max number of selections, we know that there will never be anything to the right of this one. That means we can just say that if there is a second "/", then pull the data from the right side of the cell starting after that second "/".

If there isn't a second "/", then we don't have a third selection, so the cell will remain blank.

.

To use this setup in your overall metrics, you would use the previously established COUNTIFS three times, adding them together.

 

=COUNTIFS({Supplier}, 1, {HelperA}, "organic") + COUNTIFS({Supplier}, 1, {HelperB}, "organic") + COUNTIFS({Supplier}, 1, {HelperC}, "organic")

.

The reason for having three separate functions added together as opposed to using one range for the supplier column and a second range for the three helpers is that the ranges have to all be the same size. Since the Supplier range is only one column wide, then whatever other ranges are used within that function must also be only one column wide.

.

NOTE: Because we used the LOWER function on the master sheet to convert the data before calculations, you don't nee the LOWER(@cell) function on your metrics sheet when referencing the type, but you would still need it for the Supplier Range criteria.

 

EDIT:

As an afterthought...

 

Depending on how your metrics sheet is set up and how you are wanting to display things, you may still need to use the LOWER function when referencing the Material range. If you are referencing a cell instead of entering specific text and that cell will be displayed on the chart, you would use something along the lines of...

 

=COUNTIFS({Supplier}, [criteria], {Material}, @cell = LOWER([column name]@row)) + ...................

.

When typing up the above solution I was focused on the parsing aspect. When I was giving an example of what to use on the Metrics Sheet, I forgot to scroll to the top of this thread to make it more specific to your use.

To make all of this work together, you would mesh the two solutions together.

In reply to by Paul Newcome

This is amazing Paul!

The only thing I can't seem to get is the final metrics piece,

=COUNTIFS({Supplier}, 1, {HelperA}, "organic") + COUNTIFS({Supplier}, 1, {HelperB}, "organic") + COUNTIFS({Supplier}, 1, {HelperC}, "organic")

I am assuming the {Supplier}, 1, Section is the reference to the other sheet and looking for "1" as the Supplier; however I can't seem to get this portion to function as it should.

 

I am also curious about the LEN portion of the above formulas, and how exactly it ties in to output the answers.

For the Supplier section of the metrics, you would use our previously discussed solution:

 

=COUNTIFS({Foreign Material Column Range}, FIND("organic", LOWER(@cell)) > 0,{Supplier Column Range}, LOWER(@cell) = "supplier name")

 

You would then replace the {Foreign Material Column Range} with the appropriate helper column range and the criteria can be changed to just plain old "organic".

.

The LEN function is actually pretty useful especially when parsing. What it does is give you the total count of characters in a cell including symbols and spaces.

 

For example: aaa/bbb/ccc (we will say that's in [Column Name]1)

 

The LEN function would return a result of 11 for the above example by using:

=LEN([Column Name]1)

The FIND function provides a number for where specific text appears within a cell.

=FIND(text_to_find, where_to_search, [where_to_start_search_within_search_range])

That third portion is optional, but we do leverage this when parsing.

 

To find the first "/", we use

 

=FIND("/", [Column Name]1)

 

which provides a result of 4.

To find the second "/", we want to start in the position AFTER the first "/". So we know that to find the first "/", we use

 

=FIND("/", [Column Name]1)

But this will just give us the number of 4 again. This is where we take advantage of the start position. The trick is... The first "/" will not always be in position 4, so you can't just enter the number 5 for the start position.

 

What we do then is to use the number generated by the first FIND, add 1, then use this as our stating position.

 

=FIND("/", [Column Name]1, FIND("/", [Column Name]1) + 1)

 

This gives us the number 8.

 

Now lets put that aside for now and look at the RIGHT function

=RIGHT(text, [number_of_characters])

with the number of characters being optional (if left out it will return 1 character).

 

So we know we want to start at the right of the cell to pull the final entry (the same principle is used in the MID function to determine how many characters are pulled except we subtract the first FIND from the second FIND).

 

Circling back to our LEN and FIND functions, If we take the total number of characters and subtract the number of characters up to and including that second "/", that gives us the number 3.

To get the number 3, the formula would be

 

=LEN(...........) - FIND(......, ..........., FIND(........, ............) + 1)

 

We drop this into our RIGHT function and that gives us the number of characters to pull.

=RIGHT([Column Name]1, LEN(...........) - FIND(......, ..........., FIND(........, ............) + 1))

.

The reason we use this instead of hard coding numbers is to provide the flexibility needed for words of differing length.

.

This is where thinking outside of the box comes into play. Work backwards from your intended result and break it down into as small of pieces as you can.

 

We know we want to pull characters from the right side of the cell, so we start with RIGHT. looking at that function we see that we need to specify a number of characters to pull.

So we take a look at all of the different functions that can produce a number. We can narrow our options down by looking at our variables (delimiters vs number of characters in each word).

 

We take a look at our data and realize that if we can count how many characters are in a cell and subtract from that the number of characters up to and including the second "/", we will be able to establish how many to pull.

 

So now we know we narrow our numbers functions list down even further by looking at functions that take into account characters and their relative positions within a text string.

 

This leaves us with LEN minus FIND. LEN is as basic as it can get, but we see that FIND has that optional third portion for where to start looking (which requires a number). We already know that we can narrow our list of functions down to ones that produce numbers based on characters and their positions, and we can easily rule out LEN since we are looking for something specific. That leads us back to the FIND function.

 

Now that we have broken it down into as basic of functions as we can, we can start testing each portion individually then piecing it all together.

 

When building more complex formulas, I will put each part in it's own cell. When I am nesting functions, I can then use cell references to look at the result produced by the basic function.

 

Once each part is working on it's own, I can start replacing my cell references with the functions that are actually in those cells, and there it is. A working formula.

 

One thing I still find very useful when building out the more complex messes is the template you can download from the solutions center (picture attached). It lists out all functions, what they do, and includes the ability to interact with each one so you can experiment. If you happen to mess something up on the sheet, just delete it and download a fresh template.

Okay, I see what you mean and have gotten it in and tested.  The only thing that leaves me wondering, is what do you input if you want it to count the cell but it is blank? If i put " " it reads as 0, however if i put "" then it counts everything.

That is amazing!  While I was decent at Excel (which has transferred over to this surprisingly well), I have never really had to think on large scale formulas such as this.  It is definitely cool to learn how these work and how to test out various portions to create a larger one.  I will definitely check out the sheet and see what more I can pick up!

Well if encapsulating all possible user error methods on the main sheet, there may be a cell that is skipped, in some columns it is simply a missing number but if it is the Supplier or Foreign Material column that is blank then that is important.  I would like to be able to count as a metric the number of blank cells for those columns, and if there is a way to specifically only count if the cell is blank if it has information in the cell next to it, in this way we wouldn't have to keep track of how many cells are at the bottom of the sheet.  It is less of a concern than the rest, but would still help point out issues in an easily checkable format

You could work this into your metrics sheet relatively easily.

 

Depending on what you are looking for blanks in, you could use any number of variations.

 

=COUNTIFS({Foreign Material Type Range}, ISTEXT(@cell), {Supplier Range}, ISBLANK(@cell))

 

Of course you can look at any ranges in any combination that you want.

 

You can also set up a set of checks and balances within the master sheet using the Automations feature. Set up an Automatic Update Request to send to whoever filled in the row if thy left x field blank.

 

Use conditional formatting to highlight a blank cell if another cell in the same row is populated.

You could even set up a report to show all rows that have a blank cell somewhere when another cell in the same row is populated.

There are all kinds of different ways to help avoid/account for human error.

In reply to by Paul Newcome

Thanks Paul!

Between conditional formatting for main visuals, and grabbing the metrics on the two most important columns it should provide constant and easy oversight for the whole sheet, no matter who goes to take a look at it, from Supplier to Managers.

I absolutely appreciate all the help you have given me on this and the time you have spent with me on it!  Otherwise this would have just been an idea and I understand a lot more overall about SmartSheet!

Mike