Array formula to merge several columns into one from another sheet. Is it possible?

Question

I'm using the following to pull data from a response sheet into another sheet:

=transpose(query(transpose('Form Responses 1'!BR175:BS175),,COLUMNS('Form Responses 1'!BR175:BS175))) 

This allows me to merge several columns into one. However, when new responses come in, the transpose function doesn't fill automatically because its not calculated.

source: https://www.benlcollins.com/formula-examples/array-formulas-forms/

Is it possible to implement an array from another sheet? Or is there any better way of doing this?

I need my new sheet to pull a column range from Form Responses 1, and merge all columns into a single column without creating new columns. :|

Answer 1

Please try the following instead

=ArrayFormula(Sheet1!B17:B22&" "&Sheet1!C17:C22&" "&Sheet1!D17:D22)

(Adjust ranges to your needs)

Functions used:

• ArrayFormula