5

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.

  • In the scheme, column B is text, so ISNUMBER(SEARCH("My Search", 'Form Responses 1'!$B$2:$B)) always be false, right? – Rubén Oct 1 '15 at 14:09
  • Why? The 'Form Responses 1'!$B$2:$B I'm referring to is in another worksheet – MultiformeIngegno Oct 2 '15 at 11:22
  • There's nothing wrong with the formula (at least, it works. I'm sure it can be optimized). What I was thinking is adding the ability to link these "expanded" rows to the "normal text" rows at the left (A, B and C). Does it makes sense? – MultiformeIngegno Oct 2 '15 at 11:24
  • 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. – MultiformeIngegno Oct 2 '15 at 11:27
  • Regarding my previous comment, assume that your spreadsheet had only one sheet. Regarding your other comments, the additional relevant details, like the workflow you are thinking about, should be added to the question. – Rubén Oct 2 '15 at 12:03
2

When ISNUMBER(SEARCH("My Search",'Form Responses 1'!$B$2:$B)) is TRUE the row values are not dependent on the row values of the column A, so sorting column A will not sort the value in column D for this cases.

To be display the sorted values, use SORT() or QUERY() instead of sorting in place.

An alternative is to copy and paste the values of Column D before sorting.

The same could be applied to Column E.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.