Building a Dashboard from a Report

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.

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.

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.

Happy to help!