Search code examples
google-sheets

formulatext with form submissions

I have a formsubmission sheet where I get answers sometimes that looks like this...

+5 months

this then gives me an #ERROR!

I can change it to the correct format throught this formula for example in a a second sheet

=if(istext(answers!N$2), answers!N$2, to_text(if(isnumber(answers!N$2), answers!N$2,(IFERROR(FORMULATEXT(answers!N$2))))))

Since formula text cannot be used with arrayformula I end up having to copy the formula down however when I submit another answer from my form the formula moves down as the form put another row into the sheet even though I locked the row with "$" sing.

The row should be 244 byt ut moved down to 246 because there has been submitted data. enter image description here

Is there a way of locking the row in when adding a row in the source sheet?

I have tried to use arrayformula and filter in a sheet inbetween but then it doesn't recoginse the formula but just the #ERROR! as text.

Solution

  • You can use the MAP function.

    =MAP(answers!N2:N,LAMBDA(ans,if(istext(ans), ans, to_text(if(isnumber(ans), ans,(IFERROR(FORMULATEXT(ans))))))))