Using Index to retrieve record numbers but need list descending.

[angusn]

Hello everyone,

I have a main table which is populated from a form when filled out. It generates a record number when transferring and grabs the next number in ascending order.

I have a separate list I am trying, but can't figure out how, to get to populate in descending order. The current INDEX formula I cobbled together gives what I'm looking for but in ascending order. I would like to see the list populate with the highest record number to lowest which is based on a given criteria (how many days it took to complete a task) in another cell. Below is what I have. I tried putting SORT in there but if it is an option, I can't seem to get it to work.

=IFERROR(INDEX(RecordNo,SMALL(IF(DaysToCompleteDB=$L$37,ROW(RecordNo)),ROW(1:1))-1,1),"")

Thank you.
Angus

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[angusn]

Thanks. It's been a while since I've been here but I have updated my details so it should show Office 365 on Windows (10).

 

[Fluff]

Thanks for that .

How about

=SORT(FILTER(RecordNo,DaysToCompleteDB=$L$37),,-1)

This needs to go in a normal range, NOT in a Table.

 

[angusn]

Thanks for that .

How about

=SORT(FILTER(RecordNo,DaysToCompleteDB=$L$37),,-1)

This needs to go in a normal range, NOT in a Table.

 

[angusn]

Thank you Fluff that worked great, looks like I was over thinking it.

One other thing if I may, I'm looking at this and in time I will have more entries than rows. Is it possible to put a cap on it for 48 rows?

 

[Fluff]

How about

=TAKE(SORT(FILTER(RecordNo,DaysToCompleteDB=$L$37),,-1),48)

 

[angusn]

That looks like it, thank you again fluff. First time I've seen "TAKE" so I'll have to read about that one.

I'll drink a beer and watch a rugby game as thanks.

 

[Fluff]

You're welcome & thanks for the feedback.