sort results (in order) only when selected (using a checkbox)

Question

Level 2

380 points

sort results (in order) only when selected (using a checkbox)

hello, can somebody please help me with a formula where only the results that are checked (using a tick box) are displayed in another table, in the order they are listed...

here is a screen grab of the first table (but please note even though there are some numbers, the cells are a text format)

and this is what i'd like it to look like, albeit generated automatically (i've just manually added the results in this screen grab)

thanks.

Ric

MacBook Pro 15″, macOS 12.2

Posted on Apr 6, 2022 9:14 AM

Reply

Answer 1

Best reply

Badunit

Level 7

26,012 points

Apr 6, 2022 2:25 PM in response to ric frankland

There are multiple ways to do it. Here is one that is sort-safe (i.e., does not get messed up if you sort Table 1).

Add a new column to your table.

D2 =IF(C,ROW(),9999999)

Fill down to complete the column

In your new table,

A2 =IFERROR(INDEX(Table 1::A,SMALL(Table 1::$D,ROW()−1)),"")

Fill down to complete the column

Fill across to do the next column (if you want it)

Hide Table 1 column D after it is all set up and working.

The formula in column D of Table 1 puts the row number in the cell if the checkbox is checked. Otherwise it puts the number 9999999.

The formula in Column A of Table 2 uses the SMALL function to get the row numbers from Table 1 column D and INDEX to get the data from Table 1 Column A. In cell A2, ROW()-1 will be 1 so it will get the smallest number from column D, A3 will get the 2nd smallest number, and so on.

Reply

Answer 2

ric frankland Author

Level 2

380 points

Apr 7, 2022 12:08 AM in response to Badunit

Thank you so much Badunit - perfect! I just need to remember to use the Paste Formula Results when transferring the info to another part of my spreadsheet, as I don't want them to change if I then select different options :-)

Reply