Trying to use INDEX COLLECT to return a value

Hi - I'm hoping someone can help me with this. I'm trying to create a formula that will return the remaining balance for any given request ID based on the latest date an expense was processed

Here's my data set:

And here's my formula:

=INDEX(COLLECT([Remaining Balance]:[Remaining Balance], [Request ID]:[Request ID], [Request ID]@row, [Expense Processed]:[Expense Processed], MAX([Expense Processed]:[Expense Processed])), 1)

It only works for the requests that have the latest date...how do I get the max date for just the specific request ID?

Any help would be appreciated!


Sam

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @Sam Chan,

    Try something like this:

    =INDEX(COLLECT([Remaining Balance]:[Remaining Balance], [Request ID]:[Request ID], [Request ID]@row, [Expense Processed]:[Expense Processed], MAX(COLLECT([Expense Processed]:[Expense Processed], [Request ID]:[Request ID], [Request ID]@row))), 1)

    Using your sample data above:

    Hope this helps, but if you've any problems/questions then just ask! 🙂

Answers

  • Eric Law
    Eric Law ✭✭✭✭✭✭

    @Sam Chan I think that you MAX should be on the outside of collect. So = INDEX(MAX(COLLECT([Remaining Balance]:[Remaining Balance], [Request ID]:[Request ID], [Request ID]@row, [Expense Processed]:[Expense Processed])),1)

  • Thanks @Eric Law, but I don't think the syntax is correct. I'm getting an incorrect argument message. I think it's due to the fact that there needs to be a second condition. The second condition needs to be the MAX date AND where the request IDs match. I tried changing the order of the conditions (e.g. the MAX first and then the Request ID second), but that generated an invalid value result.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @Sam Chan,

    Try something like this:

    =INDEX(COLLECT([Remaining Balance]:[Remaining Balance], [Request ID]:[Request ID], [Request ID]@row, [Expense Processed]:[Expense Processed], MAX(COLLECT([Expense Processed]:[Expense Processed], [Request ID]:[Request ID], [Request ID]@row))), 1)

    Using your sample data above:

    Hope this helps, but if you've any problems/questions then just ask! 🙂

  • @Nick Korna Works perfectly, thanks very much. Nesting another COLLECT inside the MAX was the key piece I was missing.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    No problem, happy to have helped! ☺️

  • @Nick Korna So I've tried to apply the principles of this formula into an instance where I've changed the references to cross sheet references instead of local references, and now I'm getting an #INVALID VALUE error. I can't seem to find out what I'm doing wrong as I've double checked all of the references and can't find an issue.

    Here's the sheet with the formula (in the Calculate Balance Available column: https://app.smartsheet.com/b/publish?EQBCT=513e9dd022594f808a429eca3a0b5187

    And here's the sheet it's referencing: https://app.smartsheet.com/b/publish?EQBCT=af6ad1dc5b1942779c7468b4e7562a46

    Any help from anyone would be greatly appreciated!

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    The formula should work.

    I've done a bit of trial and error though and hopefully have found the cause - is your request ID on the lookup sheet an Auto Number column? If so,this is what is breaking it but there is a solution add in an extra column with: =VALUE([Request ID]@row)

    and then use this instead of the auto-number for the cross sheet reference.

    Using only the relevant columns from the lookup sheet:

    For the cross sheet:

    The numbers aren't lined up with the request ID, but it demonstrates the theory - the autonumber is being treated as text. You can also see an example of this if you try and do a simple sum using the auto number plus a number as it will simply extend the chain. Similarly, attempting a minus will give you an invalid operation error.

    I guess the other thing that is readily apparent is might need to wrap up the whole thing in an IFERROR as well to get rid of the INVALID VALUE errors, but that should be easy enough.

    Hope this has resolved your issue - let me know how it goes!

  • @Nick Korna Yes, the Request ID is indeed an auto number column. Thanks for catching that as I didn't think that it would be treated as text rather than a number. Formula's working perfectly now, and I've wrapped it in an IFERROR to catch any other issues.

    Thanks again for your help!

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    No problem, happy to have helped! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!