Formula to pull the current auth level based on the most recent "date from" in each grouping?
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
Best Answer
-
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}
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
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.
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!
-
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)
-
Try this instead:
=INDEX({Authorization Level}, MATCH(MAX(COLLECT({Date From}, {Name}, Name@Row)), {Date From}, 0))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks Paul -Same error message
-
Can you provide a screenshot of the formula open in the sheet as if you are about to edit it?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Here you go. Thanks so much for your help!
-
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?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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
-
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}
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi Everyone,
I got it to work. Thank you all for your help!
-
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
-
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,
GenevieveJoin us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!