Most Recent Form Entries Metrics

Ryan- Impact

I have a Sheet which is populated by a form, and then calculates prices based on the entry responses. I want to create a metric on my dashboard that displays the pricing information for only the most recent entry, any tips? When I tried linking it to the cells in the top row, it seemed to be a link to that particular cell, not the column/ row reference. Any help would be much appreciated.

Paul Newcome

You could try using an INDEX/MATCH set-up and for the MATCH you could use a MAX function to look at the date. If you are using a Created (date) column, you'll be able to pull from the most recent down to the minute.

Ryan- Impact

Thanks, I had not thought of that, I will give it a try!

Paul Newcome

Happy to help. If you get stuck on any of it, feel free to let us know.

Ryan- Impact

I have never used either of those functions, Could you give me an example of how to use those how you said? I think it will work, just unsure of the syntax. Lets use {Range} for all of the data and {Created} for the created column range. 

Paul Newcome

Ok. No worries. Let's break it down so you'll be able to use it in other cases as well. We will assume cross sheet references. Column names will be BOLD. Our two sheets will be named "Form Sheet" (the sheet the forms populate) and "Metrics Sheet" (the sheet that will display the results of the formula).

 

Form Sheet Layout:

Created               Pricing

.

.

.

 

Metrics Sheet Layout:

Most Recent Date          Most Recent Pricing

.

.

.

Ranges that will be used:

{Form Sheet Range 1}: Created:Created on Form Sheet

{Form Sheet Range 2}: Pricing:Pricing on Form Sheet

.

.

.

The Most Recent Date column on the Metrics Sheet will be set as a date type column and you can put the following formula in it:

=MAX({Form Sheet Range 1})

.

.

.

In the Most Recent Pricing column on the Metrics Sheet, enter:

=INDEX({Form Sheet Range 2}, MATCH([Most Recent Date]@row, {Form Sheet Range 1}, 0))

.

.

.

Now for the meat and potatoes of how this works...

1.     The first entry in an INDEX function will be the range containing the data you          want DISPLAYED as a result of the formula.

2.     The second entry is where you determine which row it will pull from. For this          we use a MATCH function.

     2.a. The first entry in the MATCH function is the data you are looking for. In                   this case we are looking for the most recent date which has already been               established by the MAX function used in the Most Recent Date column.

     2.b.  The second entry in the MATCH function sets the range to look for the                   first entry in.

     2.c.  The third entry in the MATCH function is 0 (zero). This says that the                       range being looked in is not sorted (I have always gotten the most                           accurate results using this even if the range is actually sorted).

.

So what this does is tell Smartsheet to pull the data from the designated range (INDEX) that is also in the row where the MATCH function is TRUE.

.

.

I hope this helps explain how to use an INDEX/MATCH. It is basically a more flexible version of VLOOKUP (which I never use anymore).

 

Let me know if anything needs further explanation or clarification.

Ryan- Impact

This is exactly what I needed. Everything worked perfectly. thank you so much, I am saving this for future reference, and this should be in one of the help articles, it is super helpful. 

Paul Newcome

Happy to help and glad it was clear. Save away. I now use INDEX/MATCH exclusively (no more VLOOKUP for me) because of it's flexibility. It took me a few tries to get a feel for it, but once I did, I love it.