I have this scheme on a Google Spreadsheet:
A B C D E 1 text text text INDEX/MATCH INDEX/MATCH (expansion) 2 text text text INDEX/MATCH INDEX/MATCH (expansion) 3 text text text INDEX/MATCH INDEX/MATCH (expansion) 4 text text text INDEX/MATCH INDEX/MATCH (expansion)
So, columns A, B and C have "normal" text, column D has functions to get content from other worksheet if search matches (formula below) and the next columns (E, F, G, H ...) are just the expansion of the formulas in column D.
This is the formula in column D, repeated for each row of the column:
=IFERROR( INDEX( 'Form Responses 1'!$D$2:$V, SMALL( IF( ISNUMBER( SEARCH( "My Search", 'Form Responses 1'!$B$2:$B ) ), MATCH( ROW('Form Responses 1'!$B$2:$B), ROW('Form Responses 1'!$B$2:$B) ) ), ROW(A1) ) ), "")
I'd like to be able to sort my table, but when I try, data is restored in its initial sort. And if I add text to columns A, B and C, when I sort it changes to the order where it should be and it remains there (instead content of column D+ is restored in its initial order..)..
Is there a way to solve this? Having the content of columns A, B, C stick to the content of columns D+ and being able to sort the table?
The workflow I was thinking about is: adding values to columns A, B and C and then sort the table by values on column B for example. This is not possible because the order of values in columns D+ can't change (since it's getting the order of the other worksheet). If I try to sort the table, just the values in A,B and C change position, content in columns D+ is restored to its original position.