Sorting a Dynamic List of Data

Hi,

I have data in the attached file arranged as follows:

"Source Data" sheet
Data in columns A to L will be updated daily by pasting from another workbook. On any given day the data in columns A to L may have more or fewer rows than the day before.

"Lookup Table" sheet
The aim is to return the data from the "Source Data" sheet; the selection depends on the Data Validation choice in cell O1.

I would like to be able to sort this dynamic list of data - ideally by clicking a single button - in descending order of the values in column L.

Can someone please suggest a solution?

Thanks!

If you're already pasting from another workbook, then I would just "Format as Table" immediately after you've pasted the selection in.

Then you can Filter column N to only display "Non Lagged" and Sort column L Small=>Large.

In any case, my instinct is that Sort/Filter tools are the way to go here, rather than formula: you can easily manipulate to the output you want without having to mess around with lookup tables.

Thank you for the helpful responses.

The Lookup Table is necessary because some calculations are made on the data that's pasted into the Source Data sheet (see, for example, column F in the Lookup Table), so it's not just a case of filtering the Source Data.

If you can suggest a better solution that mine I'd be very grateful

Thanks!

Could you just copy/paste those formulas onto the right side of the input copypasta and slap them in the table, too?

In column N of the lookup table use a Rank.EQ function. So in N2 you'd have =IF(L2="","",RANK.EQ(L2,OFFSET($L$2,0,0,COUNT($L$2:$L$10)),0)) and copy it down to N10 or however many rows your dataset is ever likely to produce.

Now you can use INDEX in a separate table area to create a table that's always sorted.