Returning Indexed Values of Each Row for Latest Date
Hello, I am having trouble trying to return Current Value for each row corresponding to each name and for the latest date.
Attached is a simplified table:
Instead of manually inputting the 'Current Value', I would like to create a formula to return the latest cumulative value for each name row. I have tried using INDEX with MATCH and MAX but could not get it working for these two criteria (match name and max date). Please help and let me know whether this is doable. Thanks!
PS: To derive Cumulative Value I used the formula [Transaction Value]@row + SUM (ANCESTORS())
Best Answer
-
Update: I managed to get this solved by using SUMIFS instead 😀
Answers
-
Hi @Iva
Hope you are fine, you need to add a helper column call it "Max Value", the following is the formulas:
1- Max Value = MAX([Transaction Value]@row:[Cumulative Value]@row) convert it to column formula
2- Current Value =MAX(COLLECT([Max Value]:[Max Value], Name:Name, Name@row)) convert it to column formula
the following screenshot shows the result
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Hello @Bassam.M Khalil
Thank you for your reply. I tried it on my actual worksheet, by using helper column and MAX with COLLECT the value to be picked up would be the biggest one (instead of the value with the latest date). When there is a negative value unfortunately this method does not work.
-
@Iva
As i understand from the screenshot in your post that you need the max Value of Transaction Value & Cumulative Value. check the screenshot in your post, and please post a sample show the value you need manually and i will try to write the formula for you
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Please refer to my original post again, the highlighted part in my screenshot is the one that I asked (Current Value, not Cumulative Value); my aim is to show current value for each row corresponding to each name.
-
Update: I managed to get this solved by using SUMIFS instead 😀
-
@Iva
Perfect 👍️
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!