Search code examples
google-sheets

How to automate the Flatten function in Change Event in Google Sheets?

I can use the flatten function to view in column A: The contents of B column then below it the contents of C column.

enter image description here

But I have two problems:

  1. How to apply that for the used range in B and C columns?
  2. How to automate that result. so it happens on every change event happen in this sheet(Tab)

I tried =FLATTEN(B1:B,C1:C) but it is equal #ref. And I have tried to record a macro. but I can't define a suitable range (Because I need that to apply to the used range in B&C columns). And I don't know how to make this function calculate with every change event in the sheet.

Solution

  • Can you test this out:

    =tocol(B:C,1,1)