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

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

dave.mcpherson56751

Hi Genevieve,

That has helped me enormously, thank you so much for taking the time to look at this and come up with a solution.

Thank you

David

Genevieve P.

Hi @dave.mcpherson56751 you're very welcome!

Let me know if you're having any trouble building this out or if you need help understanding the formula more.

Cheers,

Genevieve

Andrée Starå

@dave.mcpherson56751

This might help to make the process even better. ??

We will soon introduce two major enhancements to Smartsheet forms that we’d like you to know about in advance:

1. A more immersive builder UI to help get the most out of your forms, faster
2. Conditional logic to ensure each respondent is asked the most relevant questions

All of your existing forms will continue to work and be fully editable in the new builder.

Learn more about the update here.

Would that help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

dave.mcpherson56751

Thank you as always Andree

Have been using this in EAP, think its fabulous and is going to be widely used

Stay safe

Dave

Andrée Starå

@dave.mcpherson56751

Excellent!

Happy to help!

Andrée Starå

Woho! It's live now! 😀