0

I am struggling with an automatically updating spreadsheet that is connected to a Google Form.

Whenever there is a new accomplished answer in the Form it is adding a row in the spreadsheet, resulting in a shifting of my whole spreadsheet.

Take a look at the picture to understand the problem:

Before answer

After answer

As you can see, the row is created and shifts everything apart. Is there a way, to prevent that, like protecting the area from K-P ("Preis pp." the gray-colored one until the end)?

1

You need to crate Sheet2 and move columns M:Q there and then call it back from Sheet2 with ARRAYFORMULA.

Delete everything in column K and paste this in K1 cell:

={"Preis pp.";
 ARRAYFORMULA(IF($E2:E = "Ja"; $Q$3; )+
              IF($G2:G = "Ja"; $Q$6; )+
              IF($I2:I = "Ja"; $Q$9; ))}

Delete everything in columns M, N, O, Q and paste this in M1 cell:

=ARRAYFORMULA(Tabellenblatt2!B1:F)

0


demo spreadsheet

side note: also you will need to color your cells with Conditional formatting instead of doing it manually

  • Wow thank you so much, I will try it out in a few hours – Leonhard Keßler Apr 25 at 7:07
0

If you really really want to do it without Sheet2 you can do so too. Delete columns M:O and paste in cell M1:

={""\""\""; "Zusammenfassung"\"Zusammenfassung"\"Zusammenfassung"; "Männlich:"\
"Webilich:"\"Sonstige:";COUNTIF(D:D;"männlich")\COUNTIF(D:D;"weiblich")\COUNTIFS(D$2:D; 
"<>männlich";D$2:D;"<>weiblich";D$2:D;"<>");""\""\"";"Pulli:"\"Pulli:"\"Pulli:";
COUNTIFS(D:D;"männlich";E:E;"Ja")\COUNTIFS(D:D;"weiblich";E:E;"Ja")\COUNTIFS(D$2:D; 
"<>männlich";D$2:D;"<>weiblich";D$2:D;"<>";E$2:E;"Ja");"T-Shirt:"\"T-Shirt:"\"T-Shirt:";
COUNTIFS(D:D;"männlich";G:G;"Ja")\COUNTIFS(D:D;"weiblich";G:G;"Ja")\COUNTIFS(D$2:D; 
"<>männlich";D$2:D;"<>weiblich";D$2:D;"<>";G$2:G;"Ja");"Beutel:"\"Beutel:"\"Beutel:";
COUNTIFS(D:D;"männlich";I:I;"Ja")\COUNTIFS(D:D;"weiblich";I:I;"Ja")\COUNTIFS(D$2:D; 
"<>männlich";D$2:D;"<>weiblich";D$2:D;"<>";I$2:I;"Ja");""\""\"";"Summe insgesamt"\""\"";
"Pulli"\COUNTIF(E:E;"Ja")\"";"T-Shirt"\COUNTIF(G:G;"Ja")\"";"Beutel"\COUNTIF(I:I;"Ja")\
"";""\""\"";"Größen Pulli:"\""\"";"XS"\COUNTIF(F:F;"XS")\"";"S"\COUNTIF(F:F;"S")\"";"M"\
COUNTIF(F:F;"M")\"";"L"\COUNTIF(F:F;"L")\"";"XL"\COUNTIF(F:F;"XL")\"";"XXL"\COUNTIF(F:F;
"XXL")\"";""\""\"";"Größen T-Shirt:"\""\"";"XS"\COUNTIF(H:H;"XS")\"";"S"\COUNTIF(H:H;
"S")\"";"M"\COUNTIF(H:H;"M")\"";"L"\COUNTIF(H:H;"L")\"";"XL"\COUNTIF(H:H;"XL")\"";"XXL"\
COUNTIF(H:H;"XXL")\""}

0

demo spreadsheet

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.