Formula to pull the current auth level based on the most recent "date from" in each grouping?

Todd O

I would like to collect the current Auth level into another sheet using the Name as the identifier (what formula would look at the parent and child rows together to determine the current auth for each person?) Any help would be much appreciated. Thank you

Genevieve P.

Hi @Todd O

Can I verify how you're building the formula? Are you copy/pasting from here in the Community, or building it step-by-step?

Invalid Reference usually indicates that one of the {cross sheet references} has not been set up correctly. Can you check to ensure you're looking at the correct columns for each of these:

• {Authorization Level}
• {Date From}
• {Name}

Nick Korna

Hi @Todd O,

For this you would use an INDEX MATCH in combination with a MAX COLLECT:

=INDEX({Authorization Level}, MATCH(MAX(COLLECT({Date From}, {Name}, Name@row)), {Date From}), 1)

This would find the latest date for a given name in Date From and then show the accompanying Authorization level.

However, looking at your sample data you will need to change the Date From column into Date as type. You'll also need to set up the cross sheet references as these can't be simply copy/pasted in.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Doing this should give an output like this:

Hope this helps, but if I've misunderstood anything or you've any problems/questions then let us know!

Todd O

Thank you so much for your help. I am still getting an error.

The Formula I have in Column 6 is: =INDEX({Authorization Level}, MATCH(MAX(COLLECT({Date From},{Name}, Name@Row)),{Date From}),1)

Paul Newcome

Try this instead:

=INDEX({Authorization Level}, MATCH(MAX(COLLECT({Date From}, {Name}, Name@Row)), {Date From}, 0))

Todd O

Thanks Paul -Same error message

Paul Newcome

Can you provide a screenshot of the formula open in the sheet as if you are about to edit it?

Todd O

Here you go. Thanks so much for your help!

Genevieve P.

Hi @Todd O

The @row function needs to be in all lower case. I see you have it as @Row

Can you try adjusting the R to be lower case?

Todd O

Thank you Genevieve,

This changed the error message to #invalid Ref as opposed to #unparseable. Screenshot below:

Any other solutions would be appreciated. Thank you

Genevieve P.

Hi @Todd O

Can I verify how you're building the formula? Are you copy/pasting from here in the Community, or building it step-by-step?

Invalid Reference usually indicates that one of the {cross sheet references} has not been set up correctly. Can you check to ensure you're looking at the correct columns for each of these:

• {Authorization Level}
• {Date From}
• {Name}

Todd O

Hi Everyone,

I got it to work. Thank you all for your help!