Seven Most Current Entries for each Question for Each Location

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