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

Todd O · 04/18/24

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. · 04/23/24

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 · 04/19/24

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 · 04/19/24

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 · 04/19/24

Try this instead:

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

Todd O · 04/19/24

Thanks Paul -Same error message

Paul Newcome · 04/19/24

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

Todd O · 04/19/24

Here you go. Thanks so much for your help!

Genevieve P. · 04/22/24

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 · 04/22/24

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. · 04/23/24

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 · 04/25/24

Hi Everyone,

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

Todd O · 07/15/24

Hi all,

I thought I had this formula worked out but it still pulls erroneous information. It works about 80% of the time and pulls incorrect info (not sure from where) the other 20%.

Here is the Formula I am using: =IFERROR(INDEX({Auth Level}, MATCH(MAX(COLLECT({Date From}, {Patient name}, Name@row)), {Date From}, 0)), "--")

My Goal is for the formula to return the latest Auth level in a Parent-Child set of rows (Image of the source sheet is above)

Any help would be appreciated

Genevieve P. · 07/18/24

Hi @Todd O

The filter of the person is only applied when looking for the MAX Date, meaning that if that max date exists with another person, your {Date From} column will have multiple matches:

=IFERROR(INDEX({Auth Level}, MATCH(specific date, {Date From}, 0)), "--")

The Specific Date might appear more than once - does that make sense? We've found the date based on the person, but after finding it, we're no longer applying that filter anywhere else in the formula.

I know Paul has mentioned that 2 Collects in 1 formula combination have not always worked out the best, but this is what I would personally do in this situation:

=IFERROR(INDEX(COLLECT({Auth Level}, {Patient name}, Name@row, {Date From}, MAX(COLLECT({Date From}, {Patient name}, Name@row))), 1), "--")

The easier thing to do may be to have a helper column in your source sheet to mark off the Max Date per person, and then this formula can just look for the checkbox instead of doing another MAX(COLLECT inside of your INDEX(COLLECT.

In any case, let us know if this worked for you!

Cheers,
Genevieve

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

Best Answer

Answers

Help Article Resources

Categories