Building a Dashboard from a Report

MikeCorvin

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

Paul Newcome

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

MikeCorvin

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

Paul Newcome

Happy to help!

.

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.

MikeCorvin

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?

Paul Newcome

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?

MikeCorvin

Worst case scenario would be 3 to a cell

Paul Newcome

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

 

Will it always be a / as a delimiter?

MikeCorvin

Yes it will be a / though I have not used helper columns to my knowledge as of yet, or at least not called as such.

Paul Newcome

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.

MikeCorvin

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

Paul Newcome

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...

MikeCorvin

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.

MikeCorvin

And Rubber apparently

Paul Newcome

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(Material@row, " ", ""))

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("/", MaterialHelper@row) = 0, MaterialHelper@row, LEFT(MaterialHelper@row, FIND("/", MaterialHelper@row) - 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("/", MaterialHelper@row) = 0, "", IF(FIND("/", MaterialHelper@row, FIND("/", MaterialHelper@row) + 1) > 0, MID(MaterialHelper@row, FIND("/", MaterialHelper@row) + 1, FIND("/", MaterialHelper@row, FIND("/", MaterialHelper@row) + 1) - FIND("/", MaterialHelper@row) - 1), IF(FIND("/", MaterialHelper@row) > 0, RIGHT(MaterialHelper@row, LEN(MaterialHelper@row) - FIND("/", MaterialHelper@row)))))

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("/", MaterialHelper@row, FIND("/", MaterialHelper@row) + 1) > 0, RIGHT(MaterialHelper@row, LEN(MaterialHelper@row) - FIND("/", MaterialHelper@row, FIND("/", MaterialHelper@row) + 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.