Trying to use INDEX COLLECT to return a value

Sam Chan

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

Nick Korna

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! 🙂

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)

Sam Chan

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

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! 🙂

Sam Chan

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

Nick Korna

No problem, happy to have helped! ☺️

Sam Chan

@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

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!

Sam Chan

@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

No problem, happy to have helped! 🙂