Index and Filter Functions: Workaround to sort, filter, and format within array?

Hi, I'm trying to use the index and filter functions to filter a couple columns from several based on a condition.

=INDEX(FILTER(Table1[#Data],Table1[Group]='Source Data '!L2),SEQUENCE(ROWS(FILTER(Table1[#Data],Table1[Group]='Source Data '!L2))),{27,2,3,17,7,22,20,25,31,32,33,34})

I also have conditional formatting that colors the "Name" cell based on a value to the left of it.

But the dynamic array also limits any manipulation:
1. I want to concatenate the Name and Title columns so that they're on separate lines in the same cell
2. I want to bold and increase font size of name
3. I want to be able to sort values in the array. So sort by values of names or by cell color. This isn't possible at the moment

I cannot think of any better way to dynamically list data besides filter/index. I think a VBA may be the next choice, but I've attached an example to show what I'm asking. Any guidance is appreciated. Thanks!

Hi
Do you use office365?

Hi belinda, yes I'm using 365 app (for Business)

Hi
You might want to update it in your profile so it would be easier to help.

Ok thanks, just did

Part 2 cannot be done when the cell contains a formula.
Part 3 you can sort the data in the formula, but not by colour and if you want to change the sort order or the column to be sorted you would have to change the formula.

Hi Fluff, That's what I feared. Do you think if I use Vba to just list the values off to the side, that may be a better solution for sorting?

That really depends on what you want to do. The problem with using VBA is that it doesn't update automatically unless run from some Event code and doing that could cause problems with the speed & efficiency of the workbook.

My thinking is to they can just click a button when they open the file and it'll copy the range from the filtered list, and paste the values off to the side in a table. So it doesn't need to update automatically but may be a compromise

You could use something like

Please Login or Register  to view this content.

That worked perfectly Thank you so much

You're welcome & thanks for the feedback.