yogi_Keep A Formula In Each New Record Row When Form Passes A New Record

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Delphi1 said:

When a form transfers data to its spreadsheet, it adds a new row but does not use the formula that I put in the previous record row.

I tried to fix this by copying the formula (vlookup) down 10 blank rows, but the next new record transferred from the form at row 11, below the blank rows with my formula in them.

How can I get the vlookup formula to stay in each new record?

My purpose;

Customer selects a Program Name from a drop down in the form.  The spreadsheet should then populate several cells, in the same record row, with part numbers and other lookup information using the vlookup formula.  But as each new record transfers into the spreadsheet, I have to manually copy/paste the formula into blank cells of the new record.

Any suggestions would be appreciated.

----------------------------------------------

Following is an image of the spreadsheet

Sheet1

I have created arrayformulas wherever you had computatiuons.I have created range name cross_reference as Sheet2!A4:E13 to take it out of the ResponseTable Sheet.

I have created range name cross_reference as Sheet1!E2:E80

Sheet2

formula in cell D2 is:

=ArrayFormula(transpose(split(concatenate(if(Alpha_PN=transpose(index(cross_reference,0,1)),transpose(index(cross_reference,0,2)),"")&char(9)),char(9))))

formula in cell F2 is:

=ArrayFormula(transpose(split(concatenate(if(Alpha_PN=transpose(index(cross_reference,0,1)),transpose(index(cross_reference,0,3)),"")&char(9)),char(9))))

formula in cell G2 is:

=ArrayFormula(transpose(split(concatenate(if(Alpha_PN=transpose(index(cross_reference,0,1)),transpose(index(cross_reference,0,4)),"")&char(9)),char(9))))

formula in cell O2 is:

=ArrayFormula(transpose(split(concatenate(if(Alpha_PN=transpose(index(cross_reference,0,1)),transpose(index(cross_reference,0,5)),"")&char(9)),char(9))))