Logic and summary

dave.mcpherson56751

Hi,

We have 400 locations managed by approx 30 managers. I am trying to use logic to build a form that when manager 1 chooses their name they only see sites relating to them. To achieve this i have created columns with a drop down for each manager in my sheet.

As these entries come into the sheet via form entries they go under the relevant managers column, so entries can be spread across 30 columns with the location name

Where i need help is rolling this up onto another sheet which will pull through the last entry per site.

Is this possible?

This is the feed in form

This is the summary sheet

Any help or guidance would be greatly appreciated

Thanks

Dave

Genevieve P.

Hi Dave,

You could 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 from your original Status sheet, and then list each range and criteria in the current, second sheet afterwards.

For example:

=JOIN(COLLECT({Status in Source Sheet}, {A/M in Source Sheet}, [A/M]@row, {User's Specific Column}, [Primary Column]@row))

The ranges in {these} are cross-sheet references to the column with the fixed value in your first sheet. The values in [these] represent the column in the Status Change Summary sheet, where the formula is being written.

To then only bring in the most recent data, we would want to use a MAX formula to find the most recent date with the same criteria. To do this, you would want to have two Helper columns: one in your original source sheet that is a System Created column (for when the form was submitted), and one in your Summary sheet to pull the latest submission date, so that you can easily use it in the Join(Collect formula above.

Try this in your Helper Max column, in the Summary sheet:

=MAX(COLLECT({Created Date column in Source Sheet}, {A/M in Source Sheet}, [A/M]@row, {User's Specific Column}, [Primary Column]@row))

Now once you have the Max Date, along with the other criteria, you can add this into our original JOIN(COLLECT formula to also look for the most recent entry.

Final Formula:

=JOIN(COLLECT({Status in Source Sheet}, {A/M in Source Sheet}, [A/M]@row, {User's Specific Column}, [Primary Column]@row, {Created column in Source Sheet}, [Helper Max Column]@row))

You can drag-fill this same formula down the whole status column, but as soon as it moves to a different person in the A/M column you will need to adjust the range that the {User's Specific Column} is referencing to be for that specific user. Make sure that you delete the reference and add a new one (versus clicking the "edit" button which will change the reference in your previous formulas as well).

You can read more about each of these functions in our Help Center: JOIN function / COLLECT function / @row function / Cross Sheet References / MAX function

Let me know if you have any questions as you build this out!

Cheers,

Genevieve