rank(collect function to rank based on multiple columns

Paolo Mauro

Dear Smartsheet, I wrote a function and tested it on this sheet, it should rank to also find the latest component installed based on Position & Component and then rank 1 for the most recent install date (lowest days since install). It works in this sheet with formula:

=RANKEQ([Days Since Install]@row, COLLECT([Days Since Install]:[Days Since Install], Component:Component, Component@row, Position:Position, Position@row), 1)

However when I input this formula into my other sheet in the same structure, it gives me the error #INVALID OPERATION

The funny thing is it was working in this sheet before, and then seemed to stop working, the formula is;

=RANKEQ([Days Since Install]@row, COLLECT([Days Since Install]:[Days Since Install], Teilbeschreibung:Teilbeschreibung, Teilbeschreibung@row, Motorposition:Motorposition, Motorposition@row), 1)

Thanks for any help, Paolo

Paul Newcome

Is that error present in any cell within any of the ranges?

Paul Newcome

Is that error present in any cell within any of the ranges?

Paolo Mauro

It works in one test sheet, but in my other sheets, which are already the working tables, it gives this error everywhere

Paolo Mauro

Ok, I figured it out, how annoying, there was in a reference column "days since install" which was filled for like 500 lines....and far down at the bottom of the page it had a few rows with error as no date was installed and that was messing everything up....reason why it worked before for a long time...now understand the formula is dependent on full column even if error

Paul Newcome

Happy to help. 👍️