Search code examples
arraysgoogle-sheetsgoogle-sheets-formulasubstitutiontextjoin

Google Sheet formula to compile customer details and orders

I'm using Google Form and Google Sheet for my small business. I give my customers a Google Form for them to key in their name, phone number and what their orders are and I can view all my customers' orders in the Google Sheet Form Responses.

An Example

However, sometimes the items list can be too many for me to scroll to the right and left so I made a new sheet and compiled their details and orders in a single cell using the code below:

=FormResponses1!B2&char(10)&
FormResponses1!C2&char(10)&
IF(FormResponses1!D2>0,FormResponses1!D2&" - "&FormResponses1!D1&char(10), "")&
IF(FormResponses1!E2>0,FormResponses1!E2&" - "&FormResponses1!E1&char(10), "")&
IF(FormResponses1!F2>0,FormResponses1!F2&" - "&FormResponses1!F1&char(10), "")&
IF(FormResponses1!G2>0,FormResponses1!G2&" - "&FormResponses1!G1&char(10), "")&
IF(FormResponses1!H2>0,FormResponses1!H2&" - "&FormResponses1!H1&char(10), "")&
IF(FormResponses1!I2>0,FormResponses1!I2&" - "&FormResponses1!I1&char(10), "")&
IF(FormResponses1!J2>0,FormResponses1!J2&" - "&FormResponses1!J1&char(10), "")&
IF(FormResponses1!K2>0,FormResponses1!K2&" - "&FormResponses1!K1&char(10), "")&
IF(FormResponses1!L2>0,FormResponses1!L2&" - "&FormResponses1!L1&char(10), "")&
IF(FormResponses1!M2>0,FormResponses1!M2&" - "&FormResponses1!M1&char(10), "")&
IF(FormResponses1!N2>0,FormResponses1!N2&" - "&FormResponses1!N1&char(10), "")&
IF(FormResponses1!O2>0,FormResponses1!O2&" - "&FormResponses1!O1&char(10), "")&

it goes on for at least 60 lines of IF statements to achieve the result as in the picture below

the result

The issue is that when i drag the formula down the "FormResponses1!D1" (the item name) changes to "FormResponses1!D2". So i have to change them individually. Other than that it works, but i was wondering if there is an easier way to achieve the same results. Thank you in advance!

Solution

  • to not change the frame of reference use $ in front of either column or row or both:

    $D1
D$1
$D$1

    F4 is the shortcut

    try:

    =INDEX(SUBSTITUTE(B2:B&CHAR(10)&C2:C&CHAR(10)&SUBSTITUTE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(D2:Z="",,D2:Z&"×-×"&SUBSTITUTE(D1:Z1, " ", "×"))),,9^9))), " ", CHAR(10)), "×", " "))

    enter image description here