Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have a sheet with form 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.

share|improve this question
    
You can add your logic to the white columns. The grey filled column s are reserved for the form. –  Jacob Jan Tuinstra Mar 31 '13 at 17:36
    
True, but as I said, even if there's a formula in the white column, it is not applied to new rows as their are inserted rather than data being filled into an existing row. –  Ondrej Mar 31 '13 at 17:52
    
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
add comment

1 Answer 1

up vote 1 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
add comment

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.