Seven Most Current Entries for each Question for Each Location

Amy Arnold

Hi,

I want to pull the SEVEN MOST CURRENT ENTRIES for each QUESTION for each LOCATION.

Here's my Master Smartsheet.

This is the formula I started using based on responses in the community:

=INDEX({TEST Range 2}, MATCH(MAX(COLLECT({TEST Range 4}, {TEST Range 2}, [Q1]@row)), "Clearfield", {TEST Range 5}, 0))

This formula returns a circular reference (CR).

Even with that CR fixed, this formula doesn't really give me what I need.

I have a form people are using to respond to a daily survey. The responses are added to the Master Smartsheet.

I created another Smartsheet to collect the SEVEN MOST CURRENT ENTRIES (last seven days) for each QUESTION for each LOCATION.

So I need help with a formula in the second Smartsheet that shows me the seven most current responses (last seven days) for each question, from each location, based on the date.

I almost feel like I need use the TODAY function. I tried the TODAY function in place of the MAX function and the result was INCORRECT ARGUMENT SET.

Thank you in advance

Amy Arnold

I added Days Since Response column to Smartsheet. Maybe it could be used in the formula I need to return responses for; past seven days, past six days, and so on until today is reached.

Thank you

Amy Arnold

Sorry for the back and forth.

I actually need to know the MAX (highest number) and the number last reported.

I don't need the daily numbers.

So the formula I started with just needs refinement I believe.

😀

Genevieve P.

Hi Amy,

You could potentially build this out into one larger formula, however I prefer breaking the pieces down into different sections so that if anything needs to be adjusted it's easier to update in the future. The way I would accomplish this is as follows...

MAX Number:

To find the general MAX Number based on Location, you can actually just use a MAX(COLLECT without the Index:

=MAX(COLLECT({TEST Range 2}, {TEST Range 5}, "Clearfield"))

If you have a column listing the Location in this sheet, you could have the Range 5 criteria be this [location column]@row so that you don't have to manually re-type in the location each time.

Helper MAX Date column:

Then to find out the Number Last Reported, let's first find out the most recent date from an entry for each location:

=MAX(COLLECT({TEST Range 4}, {TEST Range 5}, "Clearfield"))

This formula will need to be in a Date type of column (this can be a hidden helper column, maybe named "Max Date") and it will return the Max Date in Range 4, but only if the other range meets the criteria specified (the location).

Number Last Reported:

Once you have the MAX Date and the Location specified, then you can bring in the most recent entry for that location. You can use a JOIN(COLLECT formula for this, even though you don't have values to Join (you'll just be returning one cell's value, assuming that only one value meets all the criteria).

The way JOIN(COLLECT works is that you first list the range that has the value you want returned and then list each range and criteria in the current, second sheet afterwards.This formula would be input directly into your Q1 column in this sheet:

=JOIN(COLLECT({TEST Range 2}, {TEST Range 4}, [Max Date]@row, {TEST Range 5}, "Clearfield"))

Keep in mind that if you have more than one entry on the same MAX date for the same location, the JOIN part of this formula will bring both of those entries together into one cell.

Combination:

If you didn't want the helper column, you can build the MAX date formula right into the JOIN(COLLECT one:

=JOIN(COLLECT({TEST Range 2}, {TEST Range 4}, MAX(COLLECT({TEST Range 4}, {TEST Range 5}, "Clearfield")), {TEST Range 5}, "Clearfield"))

Here are some Help Center articles I used:

• MAX Function
• COLLECT Function
• JOIN Function
• Cross Sheet References
• @row Function

Let me know if I've misunderstood what you're looking to do, or if I can help clarify anything further!

Cheers,

Genevieve

Paul Newcome

It seems as if @Genevieve P's solution should work for what you need based on my understanding of things. If it does not...

What is the reason for collecting this data? Is it for display/informational purposes, or are you wanting to run further metrics on the data collected? How is the data entered into your master sheet?