Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a sheet with form response data in several columns, say A:G. Some of the data are numerical and I'd like to have some calculations done on them, e.g. in columns H through J. I have already found out any stretched formulas get wiped out as new lines from the form get pulled in.

So I created a new sheet, imported the data using arrayformula, but still, I don't know how to have a formula for every row in columns H:J (except the header). I can only stretch the formula within a fixed number of rows, but don't know how to make it autoadjust to newly incoming data.

I wondered if I could use a script that would be triggered with the form being submitted, adding the formulas to the new lines, but that's rather inelegant.

Sample spreadsheet: Sheet1 contains form data, Sheet2 imports them using arrayformula.

share|improve this question
    
Then you need to set them as an ARRAYFORMULA – Jacob Jan Tuinstra Mar 31 '13 at 18:18
    
Why don't you share a doc with us? – Jacob Jan Tuinstra Mar 31 '13 at 19:19
    
As I write in the question - I have tried arrayformula, but the underlying problem - the formula not stretching - still occurs. A sample spreadsheet is here: bit.ly/10rlsMI. Sheet1 contains form data, Sheet2 imports them using arrayformula. – Ondrej Mar 31 '13 at 21:38
    
An adaptation of a extract of the comment was added to the question but instead of the short URL, the spreadsheet URL was used in the link. This because the comments are not intended to be permanent. – Rubén Jan 16 at 15:22
up vote 3 down vote accepted

If you add the following formula in cell D2:

=ARRAYFORMULA(FILTER(C2:C;C2:C<>"")*1.2)

then it will take on the complete column, via the array. The FILTER function, is to make sure no empty cells are taken into account.

I've added the result into the file you shared with us.

share|improve this answer
    
Works like magic, for some reason :) Thanks! – Ondrej Apr 1 '13 at 13:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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