Building a Dashboard from a Report

MikeCorvin

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.

Paul Newcome

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.

MikeCorvin

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!

Paul Newcome

What do you mean by "count the cell but it is blank"? I was under the impression you wouldn't need to count blank cells.

MikeCorvin

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

Paul Newcome

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.

MikeCorvin

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

Paul Newcome

Happy to help!